06 조인 제거

 

조인 제거(Join Elimination) / 테이블 제거(Table Elimination)

1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는 다면, 쿼리 수행시 1쪽 테이블은 읽지 않아도 된다.

결가집합에 영향을 미치지 않기 때문이다.

?  옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환한다.

 

예)

select e.empno, e.ename, e.deptno, e.sal, e.hiredate

from   dept d, emp e

where  d.deptno = e.deptno;

 

위 쿼리에서 조인 조건식을 제외하면 1쪽 집합인 dept에 대한 참조가 전혀 없다.

따라서 아래와 같이 emp 테이블만 액세스한 것을 볼 수 있다.

 

Execution Plan

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

Plan hash value: 3956160932

 

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

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

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

|   0 | SELECT STATEMENT  |   |    14 |   350 |     4   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   350 |     4   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

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

 

오라클의 경우 이러한 쿼리 변환이 10g부터 작동하기 시작하였다.

 

아래와 같은 파라미터 또는 힌트로 해당 기능을 제어할 수 있다.

alter session set "_optimizer_join_elimination_enabled" = true ;

 

-- 힌트를 이용한 제어

select /*+ eliminate_join(d) */ e.empno, e.ename, e.deptno, e.sal, e.hiredate

from   dept d, emp e

where  d.deptno = e.deptno;

 

select /*+ no_eliminate_join(d) */ 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: 3074306753

 

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

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

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

|   0 | SELECT STATEMENT   |       |    14 |  392 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS     |         |    14 |  392 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP     |         14 |  350 |     4   (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")

 

 

조인 제거 기능이 작동하기 위한 전제 조건

PK와 FK 제약이 설정되어져 있어야 한다.

만약 PK가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고, FK가 없으면 조인에 실패하는 레코드가 존재할 수 도 있어 쿼리 변환을 수행할 수가 없다.

 

FK가 설정돼 있더라도 emp의 deptno 컬럼이 Null 허용 컬럼이면 결과가 틀리게 될 수 있다.

조인 컬럼 값이 Null인 레코드는 조인에 실패해야 정상인데 조인문을 제거하면 그 레코드들이 결과집합에 포함되기 때문이다. 그런 오류를 방지하기 위해 옵티마이저가 내부적으로 e.deptno is not null 조건을 추가해 준다.

 

Outer 조인시

10g까지는 조인 제거 기능이 수행되어지지 않으나 11g부터는 조인 제거 기능이 수행되어진다.

 

11g 테스트 결과)

SQL> 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 |   308 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   308 |     4   (0)| 00:00:01 |

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

 

 

11g 부터 PK와 FK 제약이 설정돼 있으면 in 또는 exists 서브쿼리도 일반 조인문처럼 제거 된다.