05. Outer 조인

h5.(1)Outer NL 조인

NL 조인은 그 특성상 Outer 조인할 때 방향이 한쪽으로 고정되며 Outer 기호\(+)가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택된다
(leading 힌트가 소용없다)


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       |          |        14 |   798 |         9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER    |          |        14 |   798 |         9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL    |     DEPT |         4 |    80 |         3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL    |     EMP  |         4 |   148 |         2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

습관적으로 Outer 기호\(+)를 붙인다면 성능상 불이익이 생길 수 있음을 명심

h5.(2)Outer 소트 머지 조인

Outer 소트 머지 조인도 처리 방향이 한쪽으로 고정되며, Outer 기호\(+)가 붙지 않은 테이블(Ouler 테이블)이 항상 First 테이블로 선택


select /* + use merge (d e) */
from dept d, emp e
where e.deptno(+) = d.deptno

h5.(3)Outer 해시 조인

9! Outer 기호\(+)가 붙지 않은 테이블(Outer 테이블)이 항상 Build Input으로 선택


select /*+ use hash(d e) */ d.dname, e.ename
from dept d, emp e
where e.deptno(+) = d.deptno

Execution plas
---------------------------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=14 Bytes=2K)
1 0   HASH JOIN (OUTER) (Cost=7 Card=14 Bytes=2K)
2 1         TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=4 Bytes=120)
3 1         TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=14 Bytes=1K)

10g Right Outer 해시 조인을 도입


select /*+ use hash(d e) swap_join_input(d) */ d.dname, e.ename
from dept d, emp e
where e.deptno(+) = d.deptno

Execution plas
---------------------------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=14 Bytes=280)
1 0   HASH JOIN (RIGHT OUTER) (Cost=9 Card=14 Bytes=280)
2 1         TABLE ACCESS (FULL) OF 'DEPT' (Cost=4 Card=4 Bytes=44)
3 1         TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=14 Bytes=126)

h5.(4)Full Outer 조인


SQL> create table 입금
as
select rownum   일련번호
        , round(dbms_random.value(1, 20)) 고객ID
        , round(dbms_random.value(1000, 100000),-2) 입금액
from dual connect by level <= 10

SQL> create table 출금
as
select rownum 일련번호
        , round(dbms_random.value(1, 20)) 고객아이디
        , round(dbms_random.value(1000, 10000),-2) 출금액
from dual connect by level <= 10

'Left Outer 조인 + Union All + Anti 조인 (Not Exists 필터)' 이용


select a.고객ID, a.입금액, b.출금액
  from  (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
        ,(select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
        where  b.고객ID(+) = a.고객ID
union all
select 고객ID, null, 출금액
  from  (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) a
 where not exists (select 'x' from 입금 where 고객ID = a.고객ID)

고객ID     입금액     출금액
---------- ---------- ----------
        13       6800      14500
         2      23900       6200
         3      26600       2300
        19      40400       6900
         8      95700      
         1      23100      
         6      71000      
        18      34300      
         4     121900      
        17                  9200
         7                  3900
        16                  7500
         9                  1300
         
--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows      | Bytes |     Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |        10 |   477 |        16  (63)| 00:00:01 |
|   1 |  UNION-ALL                    |          |           |       |                |          |
|*  2 |   HASH JOIN OUTER             |          |         9 |   468 |         9  (34)| 00:00:01 |
|   3 |        VIEW                   |          |         9 |   234 |         4  (25)| 00:00:01 |
|   4 |         HASH GROUP BY         |          |         9 |    63 |         4  (25)| 00:00:01 |
|   5 |          TABLE ACCESS FULL    | 입금     |        10 |    70 |         3   (0)| 00:00:01 |
|   6 |        VIEW                   |          |         8 |   208 |         4  (25)| 00:00:01 |
|   7 |         HASH GROUP BY         |          |         8 |    48 |         4  (25)| 00:00:01 |
|   8 |          TABLE ACCESS FULL    | 출금     |        10 |    60 |         3   (0)| 00:00:01 |
|   9 |   HASH GROUP BY               |          |         1 |     9 |         8  (25)| 00:00:01 |
|* 10 |        HASH JOIN ANTI         |          |         1 |     9 |         7  (15)| 00:00:01 |
|  11 |         TABLE ACCESS FULL     | 출금     |        10 |    60 |         3   (0)| 00:00:01 |
|  12 |         TABLE ACCESS FULL     | 입금     |        10 |    30 |         3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------         

h5.ANSI Full Outer 조인

9i 부터 Ansi 구문을 지원


select nvl(a.고객ID, b.고객ID) 고객ID, a.incamt, b.outamt
from  (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
       full outer join
      (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
    on a.고객ID = b.고객ID ;
    
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    17 |   884 |    28  (15)| 00:00:01 |
|   1 |  VIEW                        |      |    17 |   884 |    28  (15)| 00:00:01 |
|   2 |   UNION-ALL                  |      |       |       |            |          |
|*  3 |        HASH JOIN OUTER       |      |     9 |   612 |     9  (34)| 00:00:01 |
|   4 |         VIEW                 |      |     9 |   342 |     4  (25)| 00:00:01 |
|   5 |          HASH GROUP BY       |      |     9 |    63 |     4  (25)| 00:00:01 |
|   6 |           TABLE ACCESS FULL  | 입금 |    10 |    70 |     3   (0)| 00:00:01 |
|   7 |         VIEW                 |      |     8 |   240 |     4  (25)| 00:00:01 |
|   8 |          HASH GROUP BY       |      |     8 |    48 |     4  (25)| 00:00:01 |
|   9 |           TABLE ACCESS FULL  | 출금 |    10 |    60 |     3   (0)| 00:00:01 |
|  10 |        HASH GROUP BY         |      |     8 |    48 |    19   (6)| 00:00:01 |
|* 11 |         FILTER               |      |       |       |            |          |
|  12 |          TABLE ACCESS FULL   | 출금 |    10 |    60 |     3   (0)| 00:00:01 |
|  13 |          SORT GROUP BY NOSORT|      |     1 |     3 |     3   (0)| 00:00:01 |
|* 14 |           TABLE ACCESS FULL  | 입금 |     1 |     3 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------    

실행계획을 보면 내부적으로는 'Left Outer 조인 + Union All + Anti 조인 (Not Exists)' 방식을 그대로 사용하고 있다.
쿼리가 간단해졌을 뿐 입금과 출금 테이블을 각각 두 번씩 액세스하는 비효율은 그대로 안고 있다.

h5.Native Hash Full Outer 조인

oracle 11g에서 추가


select nvl(a.고객ID, b.고객ID) 고객ID, a.incamt, b.outamt
from  (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
       full outer join
      (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
    on a.고객ID = b.고객ID ;  
    
--------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     9 |   468 |     7  (43)| 00:00:01 |
|   1 |  VIEW                     | VW_FOJ_0 |     9 |   468 |     7  (43)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER    |          |     9 |   468 |     7  (43)| 00:00:01 |
|   3 |        VIEW               |          |     8 |   208 |     3  (34)| 00:00:01 |
|   4 |         HASH GROUP BY     |          |     8 |    48 |     3  (34)| 00:00:01 |
|   5 |          TABLE ACCESS FULL| 출금     |    10 |    60 |     2   (0)| 00:00:01 |
|   6 |        VIEW               |          |     9 |   234 |     3  (34)| 00:00:01 |
|   7 |         HASH GROUP BY     |          |     9 |    63 |     3  (34)| 00:00:01 |
|   8 |          TABLE ACCESS FULL| 입금     |    10 |    70 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------   

1.출금 테이블을 해시 테이블로 빌드 (build)
2.입금 테이블로 해시 테이블을 탐색 (Probe) 하면서 조인
3.조인 성공여부에 상관없이 결과집합에 삽입, 조인에 성공한 out레코드는 체크

h5.Union AII을 이용한 FULL Outer 조인

union all을 이용하면 버전에 상관없이 Full Outer 조인된 결과 집합을 얻을수 있다.


select 고객ID, sum(입금액) 입금액, sum(출금액) 출금액
from (
 select 고객ID, 입금액, to_number(null) 출금액
 from   입금
 union all
 select 고객ID, to_number(null) 입금액, 출금액
 from   출금
)
group by 고객ID