조인 제거

1:M조인을 하는 테이블 중 조인에만 참가하고 조건절이나 Select List에서 참조되지 않는 1쪽 테이블이 있다면 결과집합에 영향을 미치지 않으므로 M쪽 테이블만 읽도록 1쪽 테이블을 읽는 동작을 제거하는 쿼리변환이다. (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) */


================================================================
Dept, Emp테이블간의 릴레이션을 맺지않아 옵티마이져가 두 집합간의 조인카디널리티를 알 수 없을 때는 조인제거 쿼리변환이 작동하지 않는다. 

orcl:WOONG >
  1  select e.empno, e.ename, e.deptno, e.sal, e.hiredate
  2  from   dept d, emp e
  3  where  d.deptno = e.deptno;
경   과: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3009604424

-------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |
-------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    14 |   952 |
|   1 |  NESTED LOOPS      |           |    14 |   952 |
|   2 |   TABLE ACCESS FULL| EMP       |    14 |   770 |
|*  3 |   INDEX UNIQUE SCAN| DEPTNO_PK |     1 |    13 |
-------------------------------------------------------

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

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


Dept, Emp테이블간의 릴레이션을 맺어진 이후에야 쿼리변환이 작동하지 않는다. 또한 Null허용컬럼을 대상으로 조인제거를 하여 테이블을 제거하면 결과값이 달라질 수 있으므로 내부적으로 조건절(("E"."DEPTNO" IS NOT NULL)이 추가된 것을 확인할 수 있다.

orcl:WOONG >
  1  alter table dept add
  2  constraint deptno_pk primary key(deptno);


orcl:WOONG >
  1  select e.empno, e.ename, e.deptno, e.sal, e.hiredate
  2  from   dept d, emp e
  3  where  d.deptno = e.deptno;
경   과: 00:00:00.00

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

-------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
-------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   770 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   770 |
-------------------------------------------------

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

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


Null 관련 제약이나 FK제약이 필요 없는 Outer join의 경우 10g에서는 조인제거가 동작하지 않지만 11g에서는 조인제거가 정상적으로 동작한다.(어떻게 논리적인 전제가 없음에도 불구하고 동작할까?)

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

orcl:WOONG >
  1  select e.empno, e.ename, e.sal, e.hiredate
  2  from emp e, dept d
  3  where d.deptno(+) = e.deptno;
경   과: 00:00:00.00

Execution Plan
--------------------------------------------------------
Plan hash value: 1655207496

--------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |
--------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    14 |   952 |
|   1 |  NESTED LOOPS OUTER|           |    14 |   952 |
|   2 |   TABLE ACCESS FULL| EMP       |    14 |   770 |
|*  3 |   INDEX UNIQUE SCAN| DEPTNO_PK |     1 |    13 |
--------------------------------------------------------

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

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


Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

orcl:WOONG11 >
  1  select e.empno, e.ename, e.sal, e.hiredate
  2  from emp e, dept d
  3  where d.deptno(+) = e.deptno;
경   과: 00:00:00.00

Execution Plan
--------------------------------------------------

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   770 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   770 |
--------------------------------------------------

================================================================

문서에 대하여

최초작성자 : 이지웅
최초작성일 : 2010년 05월 14일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*