Full Outer Join 의 비밀

한 개발자가 Full Outer Join 에 대한 실행계획에 대하여 질문을 해왔다.
그개발자는 아래와 같은 이야기를 설명 하였다.

하나의 테이블 기준으로 outer Join 을 한다
Union all
반대편 테이블을 기준으로 다시 outer join 을 한다.

여러분들은 위의 이야기를 어떻게 생각하는가?
개발자의 이야기는 반(윗부분)은 맞고 반(아랫부분)은 틀리다.

환경은 Oracle 10g 버젼 10.2.0.4 이다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ a.empno, a.ename, b.dname
  from  emp a full outer join dept b
    on  (a.deptno= b.deptno) ;

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  VIEW                          |           |      1 |     14 |      26 |          |
|   2 |   UNION-ALL                    |           |      1 |     14 |      26 |          |
|   3 |    NESTED LOOPS OUTER          |           |      1 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |       8 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     14 |     13 |      15 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT   |     14 |     13 |       2 |          |
|   7 |    MERGE JOIN ANTI             |           |      1 |      0 |       3 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      4 |       2 |          |
|   9 |      INDEX FULL SCAN           | PK_DEPT   |      1 |      4 |       1 |          |
|* 10 |     SORT UNIQUE                |           |      4 |      4 |       1 | 2048  (0)|
|* 11 |      INDEX FULL SCAN           | IX_EMP_N2 |      1 |     13 |       1 |          |
-------------------------------------------------------------------------------------------

윗부분은 분명히 Outer 조인으로 풀렸지만 아래부분은 ANTI 조인으로 풀렸으므로 이것은 서브쿼리를 사용한것이다.
즉 옵티마이져는 아래처럼 Query Transformation 을 하게 되는 것이다.
 

select /*+ gather_plan_statistics */ *
from (
       select a.empno, a.ename, b.dname
       from  emp a, dept b
       where a.deptno = b.deptno(+)
       union all
       select null, null, a.dname
       from dept a
       where not exists (select 1
                                    from emp b
                                where  b.deptno = a.deptno
                                )
       ) ;

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  VIEW                          |           |      1 |     14 |      26 |          |
|   2 |   UNION-ALL                    |           |      1 |     14 |      26 |          |
|   3 |    NESTED LOOPS OUTER          |           |      1 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |       8 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     14 |     13 |      15 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT   |     14 |     13 |       2 |          |
|   7 |    MERGE JOIN ANTI             |           |      1 |      0 |       3 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      4 |       2 |          |
|   9 |      INDEX FULL SCAN           | PK_DEPT   |      1 |      4 |       1 |          |
|* 10 |     SORT UNIQUE                |           |      4 |      4 |       1 | 2048  (0)|
|* 11 |      INDEX FULL SCAN           | IX_EMP_N2 |      1 |     13 |       1 |          |
-------------------------------------------------------------------------------------------
실행계획이 Full Outer Join 과 똑같음을 알수 있다.
하지만 이것도 어디까지나 파라미터 _optimizer_native_full_outer_join 가 off 일때 까지의 이야기이다.

아래의 스크립트를 보자.

/*+ opt_param('_optimizer_native_full_outer_join', 'force') */

위의 힌트를 사용하거나 아니면 아래처럼 ALTER SESSION 을 하면 된다.

alter session set "_optimizer_native_full_outer_join" = 'force';

select /*+ gather_plan_statistics */
      a.empno, a.ename, b.dname
from  emp a full outer join dept b
  on  (a.deptno= b.deptno) ;

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
|   1 |  VIEW                 | VW_FOJ_0 |      1 |     14 |00:00:00.01 |      15 |          |
|*  2 |   HASH JOIN FULL OUTER|          |      1 |     14 |00:00:00.01 |      15 |  573K (0)|
|   3 |    TABLE ACCESS FULL  | DEPT     |      1 |      4 |00:00:00.01 |       7 |          |
|   4 |    TABLE ACCESS FULL  | EMP      |      1 |     14 |00:00:00.01 |       8 |          |
----------------------------------------------------------------------------------------------

_optimizer_native_full_outer_join 파라미터를 force 로 하자 ANTI 조인마져 사라졌다.
이기능은 Native hash full outer join 이라고 불리고 11g 부터는 기본이 force 로 되어있다.

결론: Full Outer Join 은 10g 버젼 까지는 Outer Join + Union all + Anti Jojn 이지만
      11g 부터는 성능이 향상된 Native hash full outer join 를 사용할수 있음을 기억하자.