Outer 조인 (by niceboyous) [2013.01.29]
05 Outer 조인
(1) Outer NL 조인
- Outer 기호(+)가 붙지 않은 테이블이 항상 드라이빙 테이블이다.
- leading 힌트 사용해도 소용없다.
## 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 |
---------------------------------------------------------------------------
## leading 힌트 있을때
select /*+ use_nl( d e ) leading( 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 |
---------------------------------------------------------------------------
h3.(2) Outer 소트 머지 조인
- 소트 머지 조인은 소트된 중간 집합을 이용, 처리 루틴은 NL 조인과 다르지 않다
- Outer NL 조인과 마찬가지로 Outer 기호(+)가 붙지 않은 테이블이 항상 First 테이블이다.
- leading 힌트 사용해도 소용없다.
select /*+ use_merge( 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 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 14 | 798 | 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 | 518 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
(3) Outer 해시 조인
9i 까지의 Outer 해시 조인
- 9i 까지 Outer 해시 조인도 Outer 기호가 붙지 않은 테이블이 항상 build Input으로 선택된다.
select /*+ use_hash( 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 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 798 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
9i까지의 Outer 해시 조인의 처리 루틴
1. outer집합 dept 테이블을 해시 테이블로 Build 한다.
2. inner 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색한다.
3. 조인에 성공한 레코드는 결과집합에 삽입하고, 해시 엔트리에 성공여부를 체크한다.
4. 탐색(Probe)단계가 끝나면 조인에 실패 했던 레코드를 포함시키기 위해, 해시 테이블을 스캔하여 체크가 없는 dept 엔트리를 결과 집합에 삽입한다
10g에서의 Outer 해시 조인(Right Outer 조인)
- Swap_join_inputs 힌트를 이용하여, Outer 기호( + )가 붙은 테이블도 Bulid Input 될 수 있다.
select /*+ use_hash( d e ) swap_join_inputs(d)*/ *
from dept d, emp e
where e.deptno = d.deptno(+);
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 14 | 798 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
- 10g Outer 해시 조인(Right Outer 조인)의 처리 루틴
1. inner 집합인 dept테이블을 해시 테이블로 Build한다.
2. outer 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색(Probe)한다.
3. 조인여부에 상관없이 결과집합에 삽입한다.
Right Outer 해시 조인 탄생 배경
- 원래 정상적인 모델상에서 보면 '고객 없는 주문' 또는 '상품 없는 주문'을 허용하지 않는다.
- 이러한 이유로 주문 테이블은 Outer 조인의 기준이 될 수 없다.
- 작은쪽 집합을 해시 테이블로 빌드하는게 성능적으로 유리하다.
- 위와 같은 이유로 Outer 집합이 해시 테이블로 Build 되는 알고리즘이 선택되었다.
- 하지만 비 정상적인 모델상 또는 프로그램 버그, 그리고 운영중 관련된 레코드를 지우지 않아
'고객 없는 주문' 또는 '상품 없는 주문'이 존재 할 수 있다. - 주문 테이블을 기준으로 고객, 상품을 Outer 조인하는 경우가 발생한다.
- 대용량인 주문 테이블이 해시테이블로 Buil되면 성능저하가 발생된다.
- 이 때문에 10g부터 Inner 집합도 해시테이블로 Build 될 수 있도록 알고리즘이 추가되었다.
(Swap_join_inputs 힌트)
(4) Full Outer 조인
-- Test를 위해 입금, 출금 Table 생성
exec dbms_random.seed(150);
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;
create table 출금
as select rownum 일련번호
, round (dbms_random.value(1,20)) 고객ID
, round (dbms_random.value(1000,10000),-2) 출금액
from dual connect by level <= 10;
exec dbms_stats.gather_table_stats('kkk','입금');
exec dbms_stats.gather_table_stats('kkk','출금');
1. '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
13 개의 행이 선택되었습니다.
------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."고객ID"(+)="A"."고객ID")
10 - access("고객ID"="고객ID")
2. Ansi Full Outer 조인
- 9i 부터 ANSI 구문 사용 가능해졌다.
- 'Left Outer 조인 + Union All + Anti 조인(Not Exists 필터)' 와 비교하여 쿼리만 간단해졌을 뿐
입금과 출금 테이블을 각각 두번씩 액세스하는 비효율은 그대로 이다.
select nvl(a.고객ID, b.고객ID) 고객ID, a.입금액, b.출금액
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 입금액 출금액
---------- ---------- ----------
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
13 개의 행이 선택되었습니다.
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 520 | 16 (25)| 00:00:01 |
| 1 | VIEW | | 10 | 520 | 16 (25)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 9 | 468 | 9 (34)| 00:00:01 |
| 4 | VIEW | | 9 | 234 | 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 | 208 | 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 | | 1 | 9 | 8 (25)| 00:00:01 |
|* 11 | HASH JOIN ANTI | | 1 | 9 | 7 (15)| 00:00:01 |
| 12 | TABLE ACCESS FULL | 출금 | 10 | 60 | 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | 입금 | 10 | 30 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."고객ID"="B"."고객ID"(+))
11 - access("고객ID"="고객ID")
3. Native Hash Full Outer 조인
- 오라클 11g에서 선보임
- 10.2.0.4버전에서 Hidden 파라미터 조정으로 이 기능 사용 가능
(_optimizer_native_full_outer_join)
select /*+ opt_param('_optimizer_native_full_outer_join','force') */ nvl (a.고객ID, b.고객ID) 고객ID, a.입금액, b.출금액
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 입금액 출금액
---------- ---------- ----------
1 23100
6 71000
13 6800 14500
2 23900 6200
4 121900
8 95700
3 26600 2300
18 34300
19 40400 6900
17 9200
7 3900
16 7500
9 1300
13 개의 행이 선택되었습니다.
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 468 | 9 (34)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 9 | 468 | 9 (34)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 9 | 468 | 9 (34)| 00:00:01 |
| 3 | VIEW | | 8 | 208 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 8 | 48 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| 출금 | 10 | 60 | 3 (0)| 00:00:01 |
| 6 | VIEW | | 9 | 234 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 9 | 63 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| 입금 | 10 | 70 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."고객ID"="B"."고객ID")
1. 출금 테이블을 해시테이블로 빌드
2. 입금테이블로 해시테이블 탐색(Probe)
3. 조인 성공과 상관없이 모두 결과집합에 삽입
4. 해시테이블에서 조인에 성공하지 못한 집합 삽입
4. Union all 을 이용한 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;
고객ID 입금액 출금액
---------- ---------- ----------
1 23100
6 71000
13 6800 14500
2 23900 6200
4 121900
8 95700
17 9200
3 26600 2300
18 34300
7 3900
19 40400 6900
9 1300
16 7500
13 개의 행이 선택되었습니다.
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 780 | 7 (15)| 00:00:01 |
| 1 | HASH GROUP BY | | 20 | 780 | 7 (15)| 00:00:01 |
| 2 | VIEW | | 20 | 780 | 6 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS FULL| 입금 | 10 | 70 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| 출금 | 10 | 60 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------