2.21_AJ_(Anti_Join)
2011.10.09 07:39
-- 참고
-- 기본 처리루틴은 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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
