2.3 OE (Outer Join Table Elimination)

불필요한 Outer쪽 테이블은 삭제하라!

 

오라클 11g에서 OE 기능이 추가됨.

이것은 OUTER 조인시 불필요한 테이블을 제거시키는 기능이다.

이 기능을 JE 기능과 분리한 이유는 PK/FK가 필요 없기 때문이다.

 

EMPLOYEE 테이블의 FK와 DEPARTMENT 테이블에서 PK CONSTRAINT를 제거)

 

--------------------------------------+-----------------------------------+---------------

| Id  | Operation                          |        Name     | Rows  | Bytes | Cost | Time |

--------------------------------------+-----------------------------------+---------------

| 0   | SELECT STATEMENT   |                       |           |            |     3 |           |

| 1   |  TABLE ACCESS FULL | EMPLOYEES|    107 |   1605 |     3 |  00:00:01 |

--------------------------------------+-----------------------------------+------------------

 

Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')

      DB_VERSION('11.1.0.6')

      FIRST_ROWS

      OUTLINE_LEAF(@"SEL$9C7001C3")

      ELIMINATE_JOIN(@"MAIN" "D"@"MAIN")

      OUTLINE(@"MAIN")

      FULL(@"SEL$9C7001C3" "E"@"MAIN")

    END_OUTLINE_DATA

  */

 

위의 PLAN을 보면 PK/FK를 제거하였음에도 UNIQUE 인덱스만 있으면 DEPARTMENT쪽을 액세스 하지 않는다. 이것이 바로 JE와 다른 점이다.

 

OE 기능을 컨트롤하는 파라미터는 JE와 같은 _optimizer_join_elimination_enabled파라미터이다.

 

Outline Data를 분석해보면 JE 기능과 마찬가지로 ELIMINATE_JOIN 힌트를 사용했음을 알 수 있다.

다시 말하면 OE는 JE 기능을 확장한 것이다.

 

10053 Trace 내용)

 

 *************************
Join Elimination (JE)   
*************************
Query block (0A8DD16C) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."EMAIL" "EMAIL","E"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "HR"."EMPLOYEES" "E","HR"."DEPARTMENTS" "D" WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+)
JE:   eliminate table: DEPARTMENTS
Registered qb: SEL$9C7001C3 0xa8dd16c (JOIN REMOVED FROM QUERY BLOCK MAIN; MAIN; "D"@"MAIN")
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$9C7001C3 nbfros=1 flg=0
    fro(0): flg=0 objn=70296 hint_alias="E"@"MAIN"    

 

l  SQL에서 Outer 조인임을 확인하고 JE 기능을 수행하였음을 알 수 있다.

l  쿼리블럭 SEL$1에서 조인이 제거되고 새로운 쿼리블럭인 SEL$9C7001C3가 생성 되었다.

l  QUERY BLOCK SIGNATURE 부분을 보면 쿼리블럭의 FROM절에는 E만 존재하며 D가 삭제

 

Outer Join Table Elimination 기능의 제약사항)

첫번째, FK가 Multi Column일 경우 기능이 수행되지 않는다.

두번째, Equal 조인 이외에 Range Join 등을 조인절에 사용하면 JE 기능이 수행되지 않는다.

세번째, Department 테이블의 PK를 제외한 컬럼을 Select 절에서 사용하면 JE 기능을 사용할 수 없다.

 

한가지 아쉬운 점)

           Select 절에 Inner 쪽의 컬럼만 사용하는 경우 Where 절에 Outer 테이블 쪽의 조건이 추가된다고 해도

결과는 동일하다. 따라서 OE 기능을 수행해도 문제가 없지만 아직 이런 기능이 없다.

 

select e.employee_id, e.email, e.department_id

  from employees e, departments d

  where e.department_id=d.department_id(+)

    and d.manager_id(+)=10;

 

-----------------------------------------+-----------------------------------+

| Id  | Operation           | Name       | Rows  | Bytes | Cost  | Time      |

-----------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |            |       |       |     7 |           |

| 1   |  HASH JOIN OUTER    |            |   107 |  2354 |     7 |  00:00:01 |

| 2   |   TABLE ACCESS FULL | EMPLOYEES  |   107 |  1605 |     3 |  00:00:01 |

| 3   |   TABLE ACCESS FULL | DEPARTMENTS|     1 |     7 |     3 |  00:00:01 |

-----------------------------------------+-----------------------------------

 

+Predicate Information:

----------------------

3 - filter("D"."MANAGER_ID"=10)

4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

 

 

 

 Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')

      DB_VERSION('11.1.0.6')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      FULL(@"SEL$1" "E"@"SEL$1")

      FULL(@"SEL$1" "D"@"SEL$1")

      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")

      USE_HASH(@"SEL$1" "D"@"SEL$1")

    END_OUTLINE_DATA

  */

 

d.manager_id(+) = 10 조건은 결과에 전혀 영향을 미치지 않지만 Outer Join Table Elimination이

수행되어지지 않았다.