CH02. 조인 원리와 활용 - 05.Outer 조인

(1)Outer NL 조인

NL 조인은 그 특성상 Outer 조인할 때 방향이 한쪽으로 고정되며, Outer기호(+)가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택됩니다. leading 힌트를 이용해 순서를 바꿔보려고 해도 소용 없습니다.

 

select /*+  leading(e) use_nl(d) */ *

from dept d, emp e

where e.deptno(+)=d.deptno;

 

select /*+ use_nl(d e) */ *

from dept d, emp e

where e.deptno(+)=d.deptno;

위아래 쿼리 같은 실행계획이 나옵니다.

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   870 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|      |    15 |   870 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

NL조인이 조인 순서에 가장 큰 영향을 받습니다. 조인 순서 때문에 성능이 나빠지지 않게 하려면 불필요한 Outer조인이 발생하지 않도록 주의해야 합니다.

 

(2) Outer 소트 머지 조인

소트 머지조인은 소트된 중간 집합을 이용한다는 점만 다를 뿐 처리루틴이 NL조인과 다르지 않습니다. 따라서 Outer 소트 머지 조인도 처리 방향이 한쪽으로 고정되며, Outer(+)가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택됩니다. leading 힌트를 이용해 순서를 바꿔보려해도 마찬가지로 되지 않습니다.

 

select /*+  use_merge(d e) */ *

from dept d, emp e

where e.deptno(+)=d.deptno;

 

select /*+  use_merge(e d) */ *

from dept d, emp e

where e.deptno(+)=d.deptno;

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    15 |   870 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |         |    15 |   870 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

 

(3) Outer 해시 조인

9i까지는 방향이 고정됐었습니다. Outer(+)기호가 붙지 않은 테이블이 항상 Build Input으로 선택되었습니다.

10g부터는 Outer NL 조인과 같은 알고리즘을 사용하는 RIGHT OUTER 조인을 사용합니다.

 

select /*+  use_hash(d e) swap_join_inputs(d) */ d.dname,e.ename

from dept d, emp e

where e.deptno=d.deptno(+);

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    14 |   308 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|      |    14 |   308 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | EMP  |    14 |   126 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

 

1. Inner 집합인 dept테이블을 해시 테이블로 빌드(build) 합니다.

2.Outer 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색(probe)합니다.

3.Outer 조인이므로 조인 성공여부에 상관없이 결과집합에 삽입합니다.

 

 

(4) Full Outer 조인

양쪽모두에게 outer join을 걸어야 하는 경우 사용합니다.