-- 참고
-- 기본 처리루틴은 exists 필터와 동일

- exists     필터 : 조인에 성공하는 (서브)레코드를 만나는 순간 결과집합에 담고 다른 (메인)레코드로 이동
- not exists 필터 : 조인에 성공하는 (서브)레코드를 만나는 순간 버리고          다른 (메인)레코드로 이동
                      조인에 성공하는 (서브)레코드가 하나도 없을 때만 결과집합에 포함

-- Semi Join <> Anti Join
-- 서브쿼리에 존재하지않는 테이터를 출력하는것

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';                  
                 
SELECT A.JOB_ID, A.DEPARTMENT_ID
  FROM EMPLOYEE A
 WHERE NOT EXISTS (SELECT 1 -- ①
                     FROM DEPARTMENT D
                    WHERE D.DEPARTMENT_ID = A.DEPARTMENT_ID);
  
ALTER SESSION SET EVENTS '10053 trace name context off';

----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |     4 |           |
| 1   |  NESTED LOOPS ANTI  |           |     1 |    16 |     4 |  00:00:01 | -- ①
| 2   |   TABLE ACCESS FULL | EMPLOYEE  |   107 |  1284 |     4 |  00:00:01 |
| 3   |   INDEX UNIQUE SCAN | DEPT_ID_PK|    27 |   108 |     0 |           |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("D"."DEPARTMENT_ID"="A"."DEPARTMENT_ID")

-- ① NOT 만 뺀다면 조인방법이 NESTED LOOPS SEMI가 나왔을 것이다.

■ Control 파라미터를 이용한 제어

_always_anti_join = choose -- OFF로 변경시 SEMI JOIN을 수행할 수 없다.(조인방법별 선택 가능)

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
--> 서브쿼리가 ANTI JOIN으로 변환가능하다는 것을 표시
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
--> SIMPLE 서브쿼리의 ANTI JOIN 변환은 COSTING 과정이 필요없음
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:   Passed validity checks.
SU:   Unnesting subquery query block SEL$2 (#2)SU: Transform ALL/NOTEXISTS subquery into a regular antijoin.
Registered qb: SEL$5DA710D3 0x4bf068 (SUBQUERY UNNEST SEL$1; SEL$2)
--> ANTI JOIN으로 변환 새로운 쿼리블럭 생성 SEL$5DA710D3

---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0
    fro(0): flg=0 objn=86728 hint_alias="A"@"SEL$1"
    fro(1): flg=0 objn=86726 hint_alias="D"@"SEL$2"
   
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block SEL$5DA710D3 (#1) that are valid to merge.
kkqctdrvTD-cleanup: transform(in-use=6212, alloc=7560) :
    call(in-use=9056, alloc=16360), compile(in-use=112248, alloc=124636), execution(in-use=1952, alloc=4060)

kkqctdrvTD-end:
    call(in-use=9056, alloc=16360), compile(in-use=104692, alloc=124636), execution(in-use=1952, alloc=4060)

SU:   Unnesting subquery query block SEL$2 (#2)SU: Transform ALL/NOTEXISTS subquery into a regular antijoin.
SJC: Considering set-join conversion in query block SEL$5DA710D3 (#1)

************************************
Cost-based predicate pushdown (JPPD)
************************************
.....중간생략
JPPD: Applying transformation directives
query block SEL$1 transformed to SEL$5DA710D3 (#1)
FPD: Considering simple filter push in query block SEL$5DA710D3 (#1)
"D"."DEPARTMENT_ID"="A"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$5DA710D3 (#1)
finally: "D"."DEPARTMENT_ID"="A"."DEPARTMENT_ID"
--> FPD 기능을 이용하여 뷰와의 조인이 아닌 테이블간의 조인으로 변경

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."JOB_ID" "JOB_ID","A"."DEPARTMENT_ID" "DEPARTMENT_ID"
FROM   "TLO"."DEPARTMENT" "D","TLO"."EMPLOYEE" "A"
WHERE  "D"."DEPARTMENT_ID"="A"."DEPARTMENT_ID"
kkoqbc: optimizing query block SEL$5DA710D3 (#1)
       
        :
    call(in-use=9492, alloc=16360), compile(in-use=108232, alloc=124636), execution(in-use=1952, alloc=4060)

kkoqbc-subheap (create addr=0x35db6c)
--> 마지막으로 COSTING 과정 수행

-- SEMI JOIN 과 매우 흡사하지만 함정이 기다리고 있다.

■ Control 파라미터를 이용한 제어

_optimizer_null_aware_antijoin      = true -- 기본 TRUE

ALTER SESSION SET "_optimizer_null_aware_antijoin" = FALSE;

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_optimizer_null_aware_antijoin      = false
Bug Fix Control Environment

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
       D.DEPARTMENT_ID, D.DEPARTMENT_NAME, LOCATION_ID
  FROM DEPARTMENT D
 WHERE D.DEPARTMENT_ID NOT IN (SELECT /*+ QB_NAME(SUB) */
                                      E.DEPARTMENT_ID
                                 FROM EMPLOYEE E)
   AND D.LOCATION_ID = 1700;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));

ALTER SESSION SET "_optimizer_null_aware_antijoin" = TRUE;

-- EMPLOYEE 테이블의 E.DEPARTMENT_ID 컬럼은 NULL값을 허용한다.
 
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      0 |00:00:00.01 |      86 |
|*  1 |  FILTER                      |                  |      0 |00:00:00.01 |      86 | -- ①
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |     21 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX |     21 |00:00:00.01 |       1 |
|*  4 |   TABLE ACCESS FULL          | EMPLOYEE         |     21 |00:00:00.01 |      84 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NULL)
   3 - access("D"."LOCATION_ID"=1700)
   4 - filter(LNNVL("E"."DEPARTMENT_ID"<>:B1))

-- ① 무언가 이상하지않은가 ANTI JOIN이 사라지고 FILTER Operation 으로 풀렸다.
-- NO ROWS RETURNED..
-- NOT IN 서브쿼리를 사용할 때 조인되는 컬럼인 DEPARTMENT_ID가 NULL 허용 컬럼이라는것에 있다.
-- ORACLE 10g의 한계 : 조인컬럼이 Null 허용컬럼이라도 Anti Join 이 실행되야 하는데 그러지 못함

-- 서브쿼리결과가 1,2,3,NULL 이라고 가정하고 NOT IN 사용할것이다.
WHERE COL1 NOT IN (1,2,3,NULL)

--논리적으로
WHERE NOT (COL1 = 1 OR COL1 = 2 OR COL1 = 3 OR COL1 = NULL)

--NOT을 빼면
WHERE COL1 <> 1
AND   COL1 <> 2
AND   COL1 <> 3
AND   COL1 <> NULL

-- 마지막으로 COL1과 NULL을 비교하고 있는데 논리적으로 비교할 수 없는 연산이다.
-- 즉, 위의 DEPARTMENT 테이블의 DEPARTMENT_ID PK로서 EMPLOYEE 테이블의 E.DEPARTMENT_ID의
   NULL가 비교될 수 없다.
-- 10g 이하 버젼에서 NOT IN 연산자를 사용할 때 항상 주의해야 하는 부분이다.

ALTER SESSION SET "_optimizer_null_aware_antijoin" = FALSE;

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
       D.DEPARTMENT_ID, D.DEPARTMENT_NAME, LOCATION_ID
  FROM DEPARTMENT D
 WHERE D.DEPARTMENT_ID NOT IN (SELECT /*+ QB_NAME(SUB) */
                                      E.DEPARTMENT_ID
                                 FROM EMPLOYEE E
                               WHERE  E.DEPARTMENT_ID IS NOT NULL)
   AND D.LOCATION_ID = 1700;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last'));

ALTER SESSION SET "_optimizer_null_aware_antijoin" = TRUE;
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     16 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS ANTI           |                   |     16 |00:00:00.01 |       8 | -- ①
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |     21 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX  |     21 |00:00:00.01 |       2 |
|*  4 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      5 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("D"."LOCATION_ID"=1700)
   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("E"."DEPARTMENT_ID" IS NOT NULL)

-- ① FILTER Operation 대신 NESTED LOOPS ANTI로 바뀌었고 일량(BUFFER) 획기적으로 줄어 들었다. 

The Logical Optimizer  (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 10월 9일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^