2.4_OJE(Outer-Join_Elimination)
2011.09.20 19:12
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을 활용 하는 것이 대부분의 경우 성능상 유리하다.
