메뉴 건너뛰기

bysql.net

2.4_OJE(Outer-Join_Elimination)

2011.09.21 04:12

AskZZang 조회 수:2774

2.4 OJE (Outer Join Elimination)

의미 없는 Outer 조인을 Inner 조인으로 바꾸어라!

 

오라클은 Outer 조인을 만나면 조건절을 분석하여 가능한 경우 Outer 조인을 Inner 조인으로 변경한다.

 

OE OJE의 가장 큰 차이점)

           OE : SQL From 절에서 Outer 쪽의 테이블을 삭제하는 기능

           OJE : Outer 조인을 Inner Join으로 바꾸는 기능

                     V7에서 처음소개, JE OE와 달리 특별한 제약사항 없음

 

)

SELECT e.employee_id, e.hire_date, e.salary, d.department_name

  FROM employees e, departments d

 WHERE e.department_id=d.department_id(+)

   AND d.location_id=1700;

 

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

| Id  | Operation                                                | Name                                 | Rows  | Bytes | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT                         |                                           |     106 |  4028 |     6 (17)          | 00:00:01 |

|*  1 |  HASH JOIN                                          |                                           |     106 |  4028 |     6 (17)          | 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS           |       21 |   399  |     2(0)              | 00:00:01 |

|*  3 |    INDEX RANGE SCAN                      | DEPT_LOCATION_IX |       21 |           |     1(0)              | 00:00:01 |

|   4 |   TABLE ACCESS FULL                       | EMPLOYEES                  |     107 |  2033 |     3(0)              | 00:00:01 |

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

 

 

Predicate Information (identified by operation id):

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

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

   3 - access("D"."LOCATION_ID"=1700)

 

위의 Plan에서 Id 기준으로 1

           Hash Join Outer가 아니라 Hash Join으로 되어 있다. => 오라클이 Outer 조인을 제거한 것.

           Outer 조인과 상관없이 LOCATION_ID = 1700 인 데이터가 나오므로 Outer 조인을 삭제해도 무방한

것이다.

Outer Join을 제거하기 위하여 OUTER_JOIN_TO_INNER 힌트를 사용.

 

 

   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$6E71C6F6")
     
OUTER_JOIN_TO_INNER(@"SEL$1")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$6E71C6F6" "D"@"SEL$1" ("DEPARTMENTS"."LOCATION_ID"))
      FULL(@"SEL$6E71C6F6" "E"@"SEL$1")
      LEADING(@"SEL$6E71C6F6" "D"@"SEL$1" "E"@"SEL$1")
      USE_HASH(@"SEL$6E71C6F6" "E"@"SEL$1")
    END_OUTLINE_DATA
  */

10053 Trace 정보)

OJE: considering predicate"D"."LOCATION_ID"=1700

OJE:      Converting outer join of DEPARTMENTS and EMPLOYEES to inner-join.
considered
Registered qb:
SEL$6E71C6F6 0x10394820 (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=70291 hint_alias="D"@"SEL$1"
    fro(1): flg=0 objn=70296 hint_alias="E"@"SEL$1"

OJE:   outer-join eliminated
JE:   Considering Join Elimination on query block SEL$6E71C6F6 (#0)

분석결과)

l  D.LOCATION_ID = 1700 조건 때문에 OJE가 발생

l  그 결과로 새로운 QUERY BLOCK SEL$6E71C6F6 이 생성

l  새로 만들어진 쿼리블럭의 From 절에는 D E가 있음을 알 수 있음

 

Outer 조인을 제대로 사용하려면 아래처럼 WHERE 조건을 바꾸어야 한다.

 

SELECT e.employee_id, e.hire_date, e.salary, d.department_name

  FROM employees e, departments d

 WHERE e.department_id=d.department_id(+)

   AND d.location_id(+)=1700;

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

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

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

| 0   | SELECT STATEMENT              |                 |       |       |     6 |           |

| 1   |  HASH JOIN OUTER              |                 |   107 |  4066 |     6 |  00:00:01 |

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

| 3   |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS     |    21 |   399 |     2 |  00:00:01 |

| 4   |    INDEX RANGE SCAN           | DEPT_LOCATION_IX|    21 |       |     1 |  00:00:01 |

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

 

Predicate Information:

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

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

4 - access("D"."LOCATION_ID"=1700)

 

 

주의사항)

           IS NULL 조건을 사용하면 OJE가 작동하지 않으므로 주의 해야 한다.

 

select e.employee_id, e.hire_date,e.salary, d.department_name

  from employees e, departments d

 where e.department_id=d.department_id(+)

   and d.location_id IS NULL

 

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

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

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

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

| 1   |  FILTER              |            |       |       |       |           |

| 2   |   HASH JOIN OUTER    |            |     1 |    38 |     7 |  00:00:01 |

| 3   |    TABLE ACCESS FULL | EMPLOYEES  |   107 |  2033 |     3 |  00:00:01 |

| 4   |    TABLE ACCESS FULL | DEPARTMENTS|    27 |   513 |     3 |  00:00:01 |

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

 

Predicate Information:

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

1 - filter("D"."LOCATION_ID" IS NULL)

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

 

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"."LOCATION_ID" IS NULL

 

rejected

OJE:   outer-join not eliminated

JE:   Considering Join Elimination on query block SEL$1 (#0)

D.DEPARTMENT_ID IS NULL 조건은 Outer 조인을 수행한 후에 판단해야 하므로 당연한 것이다.

10053 Trace를 분석하면 IS NULL 조건 때문에 OJE가 실패 했음을 알 수 있다.

 

Outer 쪽의 조건에 IS NULL이 붙는다면 Anti Join을 활용 하는 것이 대부분의 경우 성능상 유리하다.