5. Outer 조인
2011.03.28 06:55
(1) Outer NL 조인
- NL조인
- 특성상 Outer 조인할때 방향이 한쪽으로 고정
- (+)가 붙지 않은 테이블이 항상 드라이빙 (leading 로 순서 바꿔 지지 않음)
- 조인 순서에 가장 큰영향을 받으므로 불필요한 outer 조인이 발생하지 않도록 주의
SQL> select /*+ use_nl(d e) */ *
2 from dept d, emp e
3 where e.deptno(+)=d.deptno;
SQL> select /*+ use_nl(d e) */ *
2 from dept d, emp e
3 where e.deptno(+)=d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2022884187
-------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------
참고 - ERD 표기를 따르는 SQL 개발의 중요성
- ERD를 잘못 해석하면 설계의 도와 다른 데이터를 발생시킬 수도 있음
- SQL 작성시
- 각 속성의 null 값 허용여부 확인
- 엔티티 간 관계(relationship)
- 카디널리티(crow’s foot, 1:1, 1:M, M:M등) 와 Optionality 확인
예1)
사원 없는 부서가 등록될수 있으며 모든 부서가 출력되도록 하려면 outer 기호 (+)를 반드시 붙여줘야함
예2)
사원 없는 부서가 등록될수 없으며 모든 부서가 출력되도록 하려고 불필요하게
outer 기호 (+)를 드시 붙여주면 성능이 나빠질수 있음
∴ 두 예 모두 사원쪽 부서번호가 필수 컬럼이므로 불필요한 값(null등)이 입력되지 않도록
not null제약조건등을 사용하거나 프리그램 작성 단계에서 설계에 맞게 작성해야 한다.
(02) Outer 소트 머지 조인
- NL과 같으며 다만 소트된 중간 집합을 이용
- 처리 방향이 한쪽으로 고정
- Outer (+) 기호가 붙지 않은 테이블이 항상 First 테이블로 선택
- leading 힌트로 순서가 바뀌지 않음
SQL> select /*+ use_merge (d e) */ *
2 from dept d, emp e
3 where e.deptno(+)=d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2251696546
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 700 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 14 | 700 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 72 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 448 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 448 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
filter("E"."DEPTNO"(+)="D"."DEPTNO")
(3) Outer 해시 조인
- 9i 까지는 방향이 고정 ( + 기호가 없는 테이블이 Build Input으로 선택)
9i
select /*+ use_hash(d e) */ d.dname, e.ename
from dept d, emp e
where e.deptno(+)=d.deptno
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=328 Bytes=9512)
1 0 HASH JOIN (OUTER) (Cost=5 Card=328 Bytes=9512)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=12 Bytes=84)
10g
Execution Plan
----------------------------------------------------------
Plan hash value: 3713469723
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 252 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 252 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 44 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"(+)="D"."DEPTNO")
1. Outer 집합인 dept 테이블을 해시테이블로 생성(Build)
2. Inner 집합인 emp 테이블을 읽어면서 해시 테이블을 탐색(Probe)
3. 조인 성공 -> 결과 집합에 삽입, 엔트리에 성공 표시
4. 조인 완료 후 해시 테이블을 스캔하면서 체크가 없는 dept 엔트리를 결과집합에 삽입
- 조인순서 고정에 따른 문제점
- 예를 들어 주문테이블을 기준으로 고객테이블과 outer 조인하는경우
대용량인 주문테이블을 빌드하게 되므로 hash area부족에 따른
디스크 쓰기, 버킷당 엔트리 개수의 증가등이 발생할수 있어 효율이 저하되게 된다.
Right Outer 해시 조인
select /*+ use_hash(d e) swap_join_inputs(d) */ d.dname, e.ename
from dept d, emp e
where e.deptno=d.deptno(+)
9i
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=328 Bytes=9512)
1 0 HASH JOIN (OUTER) (Cost=5 Card=328 Bytes=9512)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=12 Bytes=84)
3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)
10g
Execution Plan
----------------------------------------------------------
Plan hash value: 4261033907
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 252 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 14 | 252 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 44 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO"(+))
- 방법
- Inner 집합인 dept 테이블을 해시 테이블로 빌드
- Outer 집합인 emp테이블을 읽으면서 해시테이블을 탐색
- 3.Outer 조인이므로 성공여부에 관계 없이 결과 집합에 삽입
참고 - 탄생배경
예1
예1 모델 상으로 보면 고객없는 주문, 상품없는 주문을 허용하지 않게 된다.
outer 조인이 가능한경우는 고객을 기준으로 주문, 상품을 기준으로
주문과 outer조인을 하게되므로 큰문제가 없을거라 생각해서
9i까지는 outer테이블을 해시테이블로 빌드하도록 선택
예2
하지만 운영중에서는 여러가지 이유로 ( 프로그램버그, 일부자료 삭제등)
예2와같은 경우게 생기게 되었으며 이로 인해
주문을 기준으로 고객과 상품을 outer조인할수 밖에 없는 경우가 발생하게 되어 이를 해결하기 위해
inner쪽 집합을 해시 테이블로 빌드 할수 있는 알고리즘 추가
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.주문일시
- 방법
- 주문(o)과 고객(c) 테이블을 Outer 조인할때 주문 테이블에서 PK인덱스만 빠르게 읽어 Outer조인을 완성
- 주문 (o2)테이블과 다시한번 조인하면서 Inner조인
- Build Input크기를 줄여서 디스크 쓰기 및 읽기 작업을 최소화
- 하지만 버킷당 엔트리 개수 문제는 피할수 없음
- 이때는 주문일시 구간을 나눠 쿼리를 여러번 수행하는 방법이로 처리가능
(4) Full Outer 조인
sample 생성
SQL> exec dbms_random.seed(150);
PL/SQL procedure successfully completed.
SQL> create table inc
as
select rownum serial
, round(dbms_random.value(1, 20)) cmID
, round(dbms_random.value(1000, 100000),-2) incamt
from dual connect by level <= 10; 2 3 4 5 6
Table created.
SQL> create table out
as
select rownum serial
, round(dbms_random.value(1, 20)) cmID
, round(dbms_random.value(1000, 10000),-2) outamt
from dual connect by level <= 10; 2 3 4 5 6
Table created.
SQL> exec dbms_stats.gather_table_stats(user, 'inc');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user, 'out');
PL/SQL procedure successfully completed.
‘Left Outer 조인 + Union All + Anti 조인 (Not Exists 필터)' 이용
- 입금 (inc) 과 출금 (out) 테이블을 Full Outer 조인해 고객별 입금액과 출금액을 집계할때
SQL> set autotrace on ;
SQL> select a.cmID, a.incamt, b.outamt
2 from (select cmID, sum(incamt) incamt from inc group by cmID) a
3 ,(select cmID, sum(outamt) outamt from out group by cmID) b
4 where b.cmID(+) = a.cmID
5 union all
6 select cmID, null, outamt
7 from (select cmID, sum(outamt) outamt from out group by cmID) a
8 where not exists (select 'x' from inc where cmID = a.cmID) ;
CMID INCAMT OUTAMT
---------- ---------- ----------
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
CMID INCAMT OUTAMT
---------- ---------- ----------
16 7500
9 1300
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1613876403
--------------------------------------------------------------------------------------------------
| 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 | INC | 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 | OUT | 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 | OUT | 10 | 60 | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | INC | 10 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."CMID"(+)="A"."CMID")
10 - access("CMID"="CMID")
ANSI Full Outer 조인
- 9i 부터 Ansi 구문을 지원
SQL> select nvl(a.cmID, b.cmID) cmID, a.incamt, b.outamt
from (select cmID, sum(incamt) incamt from inc group by cmID) a
full outer join
(select cmID, sum(outamt) outamt from out group by cmID) b
on a.cmID = b.cmID ; 2 3 4 5
CMID INCAMT OUTAMT
---------- ---------- ----------
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
CMID INCAMT OUTAMT
---------- ---------- ----------
9 1300
16 7500
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1455049267
-------------------------------------------------------------------------------------
| 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 | INC | 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 | OUT | 10 | 60 | 3 (0)| 00:00:01 |
| 10 | HASH GROUP BY | | 8 | 48 | 19 (6)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | TABLE ACCESS FULL | OUT | 10 | 60 | 3 (0)| 00:00:01 |
| 13 | SORT GROUP BY NOSORT| | 1 | 3 | 3 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | INC | 1 | 3 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."CMID"="B"."CMID"(+))
11 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "INC" "INC" WHERE
"CMID"=:B1 GROUP BY "CMID"))
14 - filter("CMID"=:B1)
- 쿼리만 간단해졌을 뿐 inc와 out테이블을 각각 두번식 액세스 하는 비효을은 그대로임
Native Hash Full Outer 조인
- oracle 11g 에서 추가
- 10.2.0.4 - hidden 파라미터 _optimizer_native_full_outer_join 을 조정해 사용가능
oracle 11.2.0.1.0
select nvl(a.cmID, b.cmID) cmID, a.incamt, b.outamt
from (select cmID, sum(incamt) incamt from inc group by cmID) a
full outer join
(select cmID, sum(outamt) outamt from out group by cmID) b
on a.cmID = b.cmID ;
CMID INCAMT OUTAMT
---------- ---------- ----------
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
CMID INCAMT OUTAMT
---------- ---------- ----------
16 7500
9 1300
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2412051235
--------------------------------------------------------------------------------------
| 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| OUT | 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| INC | 10 | 70 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."CMID"="B"."CMID")
**10.2.0.4
/*+ opt_param('_optimizer_native_full_outer_joinc', 'force') */
추가 해야함
- 결과로 추정
- out 테이블을 해시 테이블로 빌드 (build)
- ins 테이블로 해시 테이블을 탐색 (Probe) 하면서 조인
- 조인 성공여부에 상관없이 결과집합에 삽입, 조인에 성공한 out레코드는 체크
Union All 을 이용한 Full Outer 조인
- union all을 이용하면 버전에 상관없이 Full Outer 조인된 결과 집합을 얻을수 있다.
select cmID, sum(incamt) incamt, sum(outamt) outamt
from (
select cmID, incamt, to_number(null) outamt
from inc
union all
select cmID, to_number(null) incamt, outamt
from out
)
group by cmID ;
CMID INCAMT OUTAMT
---------- ---------- ----------
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
CMID INCAMT OUTAMT
---------- ---------- ----------
9 1300
16 7500
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 624846006
---------------------------------------------------------------------------------
| 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| INC | 10 | 70 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| OUT | 10 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
- 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
- 작성자: 남송휘 (tofriend)
- 최초작성일: 2011년 3월 27일
- 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
- 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 55 |
Front Page
 | 운영자 | 2011.02.16 | 114690 |
| 54 | 2. 인덱스 기본 원리 | balto | 2011.02.17 | 18924 |
| 53 | 1장. 인덱스 원리와 활용 | 휘휘 | 2011.02.20 | 6862 |
| 52 |
4. 테이블 Random 액세스 부하
| 휘휘 | 2011.02.26 | 7019 |
| 51 | 5. 테이블 Random 액세스 최소화 튜닝 | 휘휘 | 2011.02.26 | 9579 |
| 50 | 6. IOT, 클러스터 테이블 활용 | 오예스 | 2011.02.26 | 15827 |
| 49 |
7. 인덱스 스캔 효율
| 휘휘 | 2011.03.06 | 8565 |
| 48 | 9. 비트맵 인덱스 | 휘휘 | 2011.03.06 | 5070 |
| 47 | 8. 인덱스 설계 | AskZZang | 2011.03.09 | 5797 |
| 46 | 2. 소트 머지 조인 | 오예스 | 2011.03.20 | 7950 |
| 45 | 3. 해시 조인 | 휘휘 | 2011.03.20 | 6597 |
| 44 | 2장. 조인 원리와 활용 | 운영자 | 2011.03.22 | 3355 |
| 43 |
1. Nested Loops 조인
| 오라클잭 | 2011.03.22 | 23092 |
| 42 |
6. 스칼라 서브쿼리를 이용한 조인
| balto | 2011.03.26 | 19027 |
| » |
5. Outer 조인
| 휘휘 | 2011.03.28 | 17733 |
| 40 |
1. 인덱스 구조
| 운영자 | 2011.03.29 | 16010 |
| 39 | 4. 조인 순서의 중요성 | AskZZang | 2011.03.29 | 5375 |
| 38 | 7. 조인을 내포한 DML 튜닝 | 오예스 | 2011.04.03 | 10024 |
| 37 | 8. 고급 조인 테크닉-2 | 오라클잭 | 2011.04.04 | 12643 |
| 36 |
8. 고급 조인 테크닉-1
| 휘휘 | 2011.04.05 | 6510 |