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

이 게시물을

번호	제목	글쓴이	날짜	조회 수
60	Front Page	운영자	2011.02.16	149419
59	3. 인덱스 파티셔닝	darkbeom	2011.06.20	53824
58	3. 다양한 인덱스 스캔 방식	멋진넘	2011.02.19	33807
57	8. 통계정보 Ⅱ [1]	멋진넘	2011.04.30	31075
56	2. 파티션 Pruning	실천하자	2011.06.22	26018
55	3. 뷰 Merging	실천하자	2011.05.15	23381
54	3. 해시 조인	darkbeom	2011.03.21	21530
53	2. 서브쿼리 Unnesting	darkbeom	2011.05.16	19700
52	4. 통계정보 Ⅰ	darkbeom	2011.04.26	18091
51	7. 인덱스 스캔 효율 [1]	휘휘	2011.03.09	16889
50	7. Sort Area 크기 조정	실천하자	2011.06.14	15067
49	4. 테이블 Random 액세스 부하 [1]	darkbeom	2011.02.24	14677
48	4. 조인 순서의 중요성	운영자	2011.03.28	14240
47	1. 인덱스 구조 [1]	실천하자	2011.02.16	14190
46	1. 기본 개념	멋진넘	2011.06.28	13396
45	8. 고급 조인 테크닉-1 [1]	darkbeom	2011.04.04	13265
44	9. 비트맵 인덱스	실천하자	2011.03.06	12342
43	1. 옵티마이저	실천하자	2011.04.18	11211
42	6. 히스토그램	실천하자	2011.04.25	10917
41	6. Sort Area를 적게 사용하도록 SQL 작성	실천하자	2011.06.14	8857

첫 페이지 1 2 3 끝 페이지

쓰기

태그

bysql.net

로그인

5. Outer 조인

댓글 0