06. 조인 제거

• 1:M 관계인 두 테이블을 조인시 1쪽 테이블을 참조하지 않는다면,
• 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환한다.

• 기능을 제어하는 파라이터

alter session set "_optimizer_join_elimination_enabled" = true ;

• 힌트 eliminate_join , no_eliminate_join

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

--------------------------------------------------------------------------
| 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)

• 위 쿼리에서 dept 테이블에 대한 참조가 없기 때문에 emp 테이블만 엑세스한 것을 볼 수 있다.
• 이러한 쿼리 변환은 오라클 10g부터 작동하기 시작했고, SQL Server 등에서는 오래전부터 적용돼 온 기능이다.

• 조인제거 기능을 작동하려면 PK와 FK 제약이 설정 되어 있어야만 한다. PK가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고 FK가 없으면
• 조인에 실패하는 레코드가 존재할 수 있기 때문에 옵티마이저가 함부로 쿼리 변환을 수행할 수가 없다.

alter table dept add
constraint deptno_pk_primary key(deptno);

alter table emp add
constraint fk_deptno foreign key(deptno)
references dept(deptno);

• PK / FK 설정돼 있더라도 FK 컬럼이 NULL을 허용 컬럼이면 결과가 틀리게 될 수 있다.
• 컬럼값이 NULL인 레코드는 조인에 실패해야 정상인데 옵티마이저가 조인문을 함부로 제거하면 결과집합에 NULL값이 포함되기 때문에 결과가 틀리게 될 수 있다.
• 이런 오류를 방지하기 위해 옵티마이저가 내부적으로 FK에 IS NOT NULL 조건을 추가해 준다.

• Outer 조인할 때도 조인 제거가 가능한데 10g까지 구현되지 않았었다.

가.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 서브 쿼리에 대해서도 일반 조인문처럼 제거됨

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 |
--------------------------------------------------------------------------