2.23_OJTAJ*(Outer_Join_to_Anti_Join)
2011.10.10 12:55
2.23 OJTAJ (Outer Join to Anti Join)
Outer 조인을 Anti 조인으로 변환하라.
OJTAJ란 Outer 조인이 Anti 조인으로 변경되는 것을 말한다.
Outer 조인을 사용하면서 Outer 테이블의 조인 컬럼에 추가적으로 Is Null 조건을 사용했을 때 Anti Join으로 변환되는데, 이것이 OJTAJ의 개념이다.
SELECT e.job_id, e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
AND d.department_id IS NULL;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | NESTED LOOPS ANTI | | 106 | 1696 | 4 | 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 107 | 1284 | 4 | 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 1 | 4 | 0 | |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Plan을 보면 Nested Loops Outer 대신에 Nested Loops Anti를 사용하고 있다.
이것은 오라클이 SQL을 아래처럼 변형 시킨 것이다.
SELECT e.*
FROM employee e
WHERE NOT EXISTS (SELECT 1
FROM department d
WHERE d.department_id = e.department_id);
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 | 73 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEE | 107 | 7383 | 4 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| UQ_DEPARTMENT_ID | 27 | 108 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
Plan 정보가 Anti Join과 완전히 같음을 알 수 있다.
아래의 Outline Data를 보면 OUTER_JOIN_TO_INNER 힌트를 사용하고 있다.
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$990CD833")
ELIMINATE_JOIN(@"SEL$6E71C6F6" "D"@"SEL$1")
OUTLINE(@"SEL$6E71C6F6")
OUTER_JOIN_TO_INNER(@"SEL$1")
OUTLINE(@"SEL$1")
FULL(@"SEL$990CD833" "E"@"SEL$1")
END_OUTLINE_DATA
*/
그렇다면 이 기능은 OJE(OUTER JOIN ELIMINATION) 기능을 사용하고 있는 것일까?
Outer Join to Anti Join Conversion은 OJE 기능을 사용한 것이 아니다.
독자적인 파라미터를 가지고 있다.
변환 방식 또한 전혀 다르다.
다만 내부적으로 사용하는 힌트를 공유할 뿐이다.
아래는 해당 파리미터를 False로 만든 후 같은 SQL을 실행한 결과이다.
ALTER SESSION SET "_optimizer_outer_to_anti_enabled" = false;
explain plan for
SELECT e.job_id, e.department_id
FROM employee e, department d
WHERE e.department_id = d.department_id(+)
AND d.department_id IS NULL;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER| | 1 | 16 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEE | 107 | 1284 | 4 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| UQ_DEPARTMENT_ID | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D"."DEPARTMENT_ID" IS NULL)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
Anti Join이 사라졌고 D.DEPARTMENT_ID IS NULL 조건이 Fillter로 처리되었다.
항상 Outer Join이 Anti 조인으로 변경되어지는 것은 아니다.
제약사항
1) Select절에 Outer쪽의 컬럼이 올 수 없다.
2) Where 절의 Is Null 조건에는 Outer 쪽 테이블의 PK 컬럼만 올 수 있다.
물론 PK 컬럼으로 조인이 되어야 함은 당연한 것이다.
이 두 가지의 제약조건을 만족 해야 Outer Join to Anti Join Conversion이 발생한다.
Anti Join과 관련된 10053 Trace 정보는 다음과 같다.
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "E"."JOB_ID" "JOB_ID","E"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "HR"."EMPLOYEE" "E","HR"."DEPARTMENT" "D" WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+) AND "D"."D EPARTMENT_ID" IS NULL
SQL:******* UNPARSED QUERY IS *******
SELECT "E"."JOB_ID" "JOB_ID","E"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "HR"."EMPLOYEE" "E","HR"."DEPARTMENT" "D" WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+) AND "D"."D EPARTMENT_ID" IS NULL
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Considering outer-join elimination on query block SEL$1 (#0)
OJE: considering predicate"E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+)
rejected
OJE: considering predicate"D"."DEPARTMENT_ID" IS NULL
OJE: Converting outer join of DEPARTMENT and EMPLOYEE to anti-join.
considered
Registered qb: SEL$6E71C6F6 0x677e84 (OUTER-JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$6E71C6F6 nbfros=2 flg=0
fro(0): flg=0 objn=89861 hint_alias="D"@"SEL$1"
fro(1): flg=0 objn=89860 hint_alias="E"@"SEL$1"
OJE: outer-join eliminated
JE: Considering Join Elimination on query block SEL$6E71C6F6 (#0)
# Trace 정보를 보면 D.DEPARTMENT_ID IS NULL 조건에 의해서 Anti Join으로 변환된 것을 알 수 있다.
대부분의 경우 Outer Join to Anti Join Conversion이 적용되면 더 나은 성능을 보인다.
왜냐하면 Filter로 처리될 경우는 선택할 수 있는 Operation이 고정적이지만 Anti Join은 Nested
Loop/Merge/Hash Join 중에서 가장 유리한 것을 선택할 수 있기 때문이다.
OJTAJ 기능을 제어하는 파라미터는 _optimizer_outer_to_anti_enabled 이며 Default로 True이다.
힌트로는 OUTER_JOIN_TO_INNER / NO_OUTER_JOIN_TO_INNER를 사용할 수 있다.
