5. Outer 조인 ( Test 환경 - Windows 7, Oracle 11g2R, SQL*Plus: Release 11.2.0.1.0 )
(1) Outer NL 조인
select /*+ use_nl(d e) */ *
from dept d, emp e
where e.deptno(+) = d.deptno;
Execution Plan
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 14 | 812 | 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPTNO"(+)="D"."DEPTNO")
(2) Outer 소트 머지 조인
select /*+ use_merge(d e) */ *
from dept d, emp e
where e.deptno(+) = d.deptno;
Execution Plan
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 14 | 812 | 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 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
filter("E"."DEPTNO"(+)="D"."DEPTNO")
(3) Outer 해시 조인
select /*+ use_hash(d e) */ *
from dept d, emp e
where e.deptno(+) = d.deptno;
select /*+ use_hash(d e) swap_join_inputs(d) */ *
from dept d, emp e
where e.deptno = d.deptno(+);
- 9i 이전 버전에서 Outer 해시 조인 튜닝
select /*+ order index_ffs (o) full (c ) full (o2) use_hash (o c) use_hash (o2)
parallel_index(o) parallel(c) parallel(o2) */ c.*,o2.*
from 주문 o, 고객 c, 주문 o2
where c.고객번호(+)=o.고객번호
and o2.고객번호 = o.고객번호
and o2.상품번호 = o.상품번호
and o2.주문일시 = o.주문일시
(4) Full Outer 조인
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(user, '입금');
exec dbms_stats.gather_table_stats(user, '출금');
- 'Left Outer 조인 + Union All + Anti 조인(Not Exists 필터)' 이용
SQL> select a.고객ID, a.입금액, b.출금액
2 from (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
3 ,(select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
4 where b.고객ID(+) = a.고객ID
5 union all
6 select 고객ID, null, 출금액
7 from (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) a
8 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 개의 행이 선택되었습니다.
Execution Plan
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 540 | 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 | | 8 | 72 | 8 (25)| 00:00:01 |
|* 10 | HASH JOIN ANTI | | 10 | 90 | 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")
SQL> select nvl(a.고객ID, b.고객ID) 고객ID, a.입금액, b.출금액
2 from (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
3 full outer join
4 (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
5 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 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------------------------------
| 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")
- Native Hash Full Outer 조인
SQL> select /*+ opt_param('_optimizer_native_full_outer_join', 'force') */
2 nvl(a.고객ID, b.고객ID) 고객ID, a.입금액, b.출금액
3 from (select 고객ID, sum(입금액) 입금액 from 입금 group by 고객ID) a
4 full outer join
5 (select 고객ID, sum(출금액) 출금액 from 출금 group by 고객ID) b
6 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 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------------------------------
| 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")
- Union All을 이용한 Full Outer 조인
SQL> select 고객ID, sum(입금액) 입금액, sum(출금액) 출금액
2 from (
3 select 고객ID, 입금액, to_number(null) 출금액
4 from 입금
5 union all
6 select 고객ID, to_number(null) 입금액, 출금액
7 from 출금
8 )
9 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 개의 행이 선택되었습니다.
Execution Plan
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 261 | 7 (15)| 00:00:01 |
| 1 | HASH GROUP BY | | 9 | 261 | 7 (15)| 00:00:01 |
| 2 | VIEW | | 20 | 580 | 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 |
-----------------------------------------------------------------------------
- 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
- 작성자: 위충환 (실천하자)
- 최초작성일: 2011년 03월 29일
- 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
- 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^