조인제거란

• 1:M관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면,옵티마이저는 이 특성을 이용하여 M쪽 테이블만 읽도록 쿼리를 변환하는 기능(10g부터 작동)

조인제거를 위한 조건

• PK 존재 : 조인을 하려는 두 테이블간의 조인 ㄱ카디널리티를 파악하기 위해서 필수
• FK 존재 : NULL을 제외한 레코드 중 조인이 실패하는 레코드가 없다는 것을 보장 (NULL에 대한 처리를 위해 옵티마이져는 내부적으로 IS NOT NULL조건을 추가)

관련 파라미터 및 힌트(조인제거 기능의 turn on/off)

• alter session set "_optimizer_join_elimination_enabled" = true;
• /*+ eliminate_join(d) */
• /*+ no_eliminate_join(d) */

 

select e.empno,e.ename,e.deptno,e.sal,e.hiredate
from dept d, emp e
where d.deptno=e.deptno;


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   350 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   350 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPTNO" IS NOT NULL)

Outer 조인시 조인제거

 
- 10g에서는 제거되지 않는다

select e.empno, e.ename, e.sal, e.hiredate
from emp e, dept d
where d.deptno(+) =e.deptno ;

Execution Plan
----------------------------------------------------------
Plan hash value: 438843259

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   392 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|         |    14 |   392 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   350 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPTNO"(+)="E"."DEPTNO")

- 11g에서는 정상적으로 제거된다
- 11g에서는 PK/FK 제약이 설정되어 있으면 in/exists 서브 쿼리에 대해서도 일반 조인문처럼 제거됨

-Outer
select e.empno, e.ename, e.sal, e.hiredate
from emp e, dept d
where d.deptno(+) =e.deptno ;


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   588 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   588 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

-IN 
select * from emp e
where deptno in (select /*+ eliminate_join(dept) */ deptno from dept);


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |  1218 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)


-EXISTS
select * from emp e
where exists (select /*+ eliminate_join(dept) */ 'X' from dept where deptno=e.deptno);

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |  1218 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |  1218 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPTNO" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)