5. Outer 조인

2011.03.30 03:11

실천하자 조회 수:5029

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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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")

ERD 표기를 따르는 SQL 개발의 중요성

(2) Outer 소트 머지 조인

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

from dept d, emp e

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

Execution Plan

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 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 입금

select rownum 일련번호

, round(dbms_random.value(1, 20)) 고객ID

, round(dbms_random.value(1000, 100000),-2) 입금액

from dual connect by level <= 10;

create table 출금

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

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 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")

ANSI Full Outer 조인

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

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 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
문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^

이 게시물을

번호	제목	글쓴이	날짜	조회 수
40	10. 실체화 뷰 쿼리로 재작성	suspace	2011.06.07	5531
39	9. Outer 조인을 Inner 조인으로 변환	darkbeom	2011.06.07	7834
38	8. 공통 표현식 제거	darkbeom	2011.06.07	5223
37	12. 기타 쿼리 변환 [3]	실천하자	2011.06.03	6646
36	4. 조건절 Pushing	실천하자	2011.05.31	7021
35	7. OR-Expansion	멋진넘	2011.05.31	8720
34	6. 조인 제거	멋진넘	2011.05.30	4595
33	5. 조건절 이행	휘휘	2011.05.30	5407
32	2. 서브쿼리 Unnesting	darkbeom	2011.05.16	19700
31	3. 뷰 Merging	실천하자	2011.05.15	23382
30	7. 비용	휘휘	2011.05.03	6170
29	1. 쿼리 변환이란?	실천하자	2011.05.02	4927
28	8. 통계정보 Ⅱ [1]	멋진넘	2011.04.30	31082
27	5. 카디널리티	suspace	2011.04.26	5953
26	4. 통계정보 Ⅰ	darkbeom	2011.04.26	18093
25	6. 히스토그램	실천하자	2011.04.25	10917
24	3. 옵티마이저의 한계	멋진넘	2011.04.19	7857
23	1. 옵티마이저	실천하자	2011.04.18	11211
22	2. 옵티마이저 행동에 영향을 미치는 요소	휘휘	2011.04.18	5094
21	8. 고급 조인 테크닉-2	suspace	2011.04.05	7017

첫 페이지 1 2 3 끝 페이지

쓰기

태그

bysql.net

로그인

5. Outer 조인

댓글 0