<오라클 성능 고도화 원리와 해법2> Ch04-06 조인 제거

오라클 성능 고도화 원리와 해법2 - Ch04-06 조인 제거

1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 쿼리 수행 시 1쪽 테이블은 읽지 않아도 된다. 결과 집합에 영향을 미치지 않기 때문이다. 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 이를 ‘조인 제거(Join Elimination)’ 또는 ‘테이블 제거(Table Elimination)’라고 한다.

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

ROWS     Row Source Operation
------   ----------------------------------------------
     14  TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=58 us)

위 쿼리에서 조인 조건식을 제외하면 1쪽 집합인 dept에 대한 참조가 전혀 없다(스크립트 ch4_18.kt 참조). 따라서 emp 테이블만 액세스한 것을 볼 수 있다. 이러한 쿼리 변환이 오라클의 경우 10g부터 작동하기 시작했지만 SQL Server 등에서는 이미 오래 전부터 적용된 기능이다.

이 기능을 제어하는 파라미터는 아래와 같으며, eliminate join과 no_eliminate_join 힌트를 통해 쿼리 레벨에서 제어할 수도 있다.

alter session set "_optimizer_join_elimination_enabled" = true;

조인 제거(Join Elimination) 기능이 작동하려면 아래와 같이 PK와 FK 제약이 설정돼 있어야만 한다. 이는 옵티마이저가 쿼리 변환을 수행하기 위한 지극히 당연한 조건이다. 만약 PK가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고, FK가 없으면 조인에 실패하는 레코드가 존재할 수도 있어 옵티마이저가 함부로 쿼리 변환을 수행할 수가 없다.

SQL> alter table dept add
  2  constraint deptno_pk primary key (deptno);

SQL> alter table emp add
  2  constraint fk_deptno foreign key (deptno)
  3  references dept (deptno);

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

11g부터 PK와 FK 제약이 설정돼 있으면 아래와 같은 in 또는 exists 서브쿼리도 일반 조인문처럼 제거된다. 똑같이 optimizer_join_elimination_enabled 파라미터에의해 영향을 받고, eliminate join, no_eliminate join 힌트를 통한 제어도 가능하다.

select * from emp e
where deptno in (select /*+ eliminate join (dept) */ deptno from dept);

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