한 개발자가 Full Outer Join 에 대한 실행계획에 대하여 질문을 해왔다.
그개발자는 아래와 같은 이야기를 설명 하였다.
하나의 테이블 기준으로 outer Join 을 한다
Union all
반대편 테이블을 기준으로 다시 outer join 을 한다.
여러분들은 위의 이야기를 어떻게 생각하는가?
개발자의 이야기는 반(윗부분)은 맞고 반(아랫부분)은 틀리다.
환경은 Oracle 10g 버젼 10.2.0.4 이다.
아래의 SQL 을 보자.
-------------------------------------------------------------------------------------------
| 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 을 하게 되는 것이다.
-------------------------------------------------------------------------------------------
| 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 일때 까지의 이야기이다.
아래의 스크립트를 보자.
----------------------------------------------------------------------------------------------
| 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 를 사용할수 있음을 기억하자.
그개발자는 아래와 같은 이야기를 설명 하였다.
하나의 테이블 기준으로 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) ;
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
)
) ;
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) ;
위의 힌트를 사용하거나 아니면 아래처럼 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 를 사용할수 있음을 기억하자.
'Oracle > Data Join Method' 카테고리의 다른 글
Nested Loop Join 성능향상과 관련된 2가지 원리 (17) | 2009.05.09 |
---|---|
Hash Join Right (Semi/Anti/Outer) 의 용도 (17) | 2009.03.02 |
Full Outer Join 의 비밀 (4) | 2009.02.23 |
Hash 조인시 Bushy tree Plan 유도하기 (4) | 2008.11.01 |
Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) (2) | 2008.10.30 |
Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter) (5) | 2008.10.12 |
TAG Anti Join,
Full Outer Join,
full outer join 튜닝,
Native hash full outer join,
optimizer,
outer join,
Query Transforamtion,
아우터 조인
댓글을 달아 주세요
10.2.0.4 PatchSet에서 Native Full Outer Join 기능이 추가되었군요. 10.2.0.4에서는 기본값이 off인가요? choose인가요?
확인결과 OFF 로 되어 있습니다.
감사합니다.
박우진 2010.02.17 17:37 댓글주소 수정/삭제 댓글쓰기
양쪽다 OUTER한 셋으로 FULL OUTER 결과를 만드려면 LEFT JOIN OUTER UNION RIGHT JOIN OUTER 하면 되겠죵~
지당한 이야기 입니다.
만나서 반갑습니다.