메뉴 건너뛰기

bysql.net

2.23_OJTAJ*(Outer_Join_to_Anti_Join)

AskZZang 2011.10.10 13:25 조회 수 : 1683

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 JoinAnti 조인으로 변경되어지는 것은 아니다.

 

제약사항

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 이며 DefaultTrue이다.

힌트로는 OUTER_JOIN_TO_INNER / NO_OUTER_JOIN_TO_INNER를 사용할 수 있다.