오라클 성능 고도화 원리와 해법 II (2016년)
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
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3343
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.