5. Outer 조인 (by manon94) [2010.08.30]
05 Outer 조인
(1) Outer NL 조인
- NL 조인은 그 특성상 Outer 조인 할때 방향이 한쪽으로 고정된다.
- Outer 기호( + )가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택된다.
- Leading 이나 Ordered 힌트로 그 순서를 바꿀 수 없다.
Nested Loop Outer
SQL> SELECT *
2 FROM dept d, emp e
3 WHERE e.deptno(+) = d.deptno
4 ;
13 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1350698460
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 684 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 12 | 684 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 4 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1600 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
(2) Outer 소트 머지 조인
- 소트머지 조인은 소트된 중간집합을 이용한다는 점만 다를뿐 처리루틴은 NL조인과 다르지 않다.
- 즉, 그 특성상 Outer 조인 할때 방향이 한쪽으로 고정된다.
- Outer 기호( + )가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택된다.
- Leading 이나 Ordered 힌트로 그 순서를 바꿀 수 없다.
Merge Join Outer
SQL> SELECT /*+ use_merge(d e) */ *
2 FROM dept d, emp e
3 WHERE e.deptno(+) = d.deptno
4 ;
13 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 2251696546
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 684 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 12 | 684 | 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 | | 12 | 444 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 12 | 444 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
filter("E"."DEPTNO"(+)="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1592 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processed
(3) Outer 해시 조인
9i 까지의 Outer 해시 조인
- Outer 기호( + )가 붙지 않은 테이블이 항상 Bulid Input 테이블로 선택된다.
- Leading 이나 Ordered 힌트로 그 순서를 바꿀 수 없다.
Hash Join Outer
SQL> SELECT /*+ use_hash(d e) */ *
2 FROM dept d, emp e
3 WHERE e.deptno(+) = d.deptno
4 ;
13 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3713469723
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 684 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 12 | 684 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 12 | 444 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"(+)="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1684 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
- 그림_2_19
- 그림_2_20
10g 의 Right Outer 해시 조인
- Outer 기호( + )가 붙은 테이블도 Bulid Input 될 수 있다.
- Swap_join_inputs 힌트로 그 순서를 바꿀 수 있다.
Hash Join Right Outer
SQL> SELECT /*+ use_hash(d e) swap_join_inputs(d) */ *
2 FROM dept d, emp e
3 WHERE e.deptno = d.deptno(+)
4 ;
12 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 4261033907
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 684 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 12 | 684 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 12 | 444 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1650 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
- 차이점 비교
| 9i Hash Join Outer | 10g Hash Join Right Outer | ||
|---|---|---|---|
| 1. Outer 집합인 Dept 테이블을 해시테이블로 Build한다. | 1. Inner 집합인 Dept 테이블을 해시테이블로 Build한다. | 2. Inner 집합인 Emp 테이블을 읽으면서 해시테이블 탐색(Probe) | 2. Outer 집합인 Emp 테이블을 읽으면서 해시테이블 탐색(Probe) |
| 3. 조인에 성공한 자료를 결과집합에 삽입하고 해쉬테이블에 성공여부 체크 | 3. 조인 성공여부와 상관없이 결과집합에 삽입 | ||
| 4. 해시테이블에서 조인에 실패한 자료를 결과집합에 삽입 |
Right Outer 해시 조인 탄생 배경
- 그림_2_21
- 정상적인 설계 모델에 있어서 고객없는 주문, 상품 없는 주문은 없다.
- 따라서 주문은 아우터 조인의 기준집합이 될 수 없다.
- 적은 량의 자료인 고객, 상품이 기준이 되어 아우터 조인하는 것이 유리함
- 따라서 Outer 집합이 해시테이블로 빌드되는 알고리즘을 선택
- 그림_2_22
- 비정상적 설계 모델에서는 고객없는 주문, 상품 없는 주문이 존재할 수 있다.
- 주문을 기준으로 고객이나 상품을 아우터 조인하는 경우가 발생
- 큰 집합인 주문이 해시테이블로 빌드되면서 성능저하 발생
- 이에 Inner 집합도 해시테이블로 빌드될수 있도록 알고리즘 추가
9i 이전 버전에서 Outer 해시 조인 튜닝
Outer 해시 조인 튜닝
SQL> CREATE INDEX idx_emp2 ON emp(empno, deptno);
인덱스가 생성되었습니다.
SQL>
SQL> SELECT /*+ ordered index_ffs(e) full(d) full(e2) use_hash(e d) use_hash(e2)
2 parallel_index(e) parallel(d) parallel(e2) */
3 d.*, e2.*
4 FROM emp e, dept d, emp e2
5 WHERE e.deptno = d.deptno(+)
6 AND e2.empno = e.empno
7 ;
12 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 2161030882
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 768 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 768 | 9 (12)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 12 | 324 | 6 (17)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IDX_EMP2 | 12 | 84 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 12 | 444 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E2"."EMPNO"="E"."EMPNO")
2 - access("E"."DEPTNO"="D"."DEPTNO"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
1650 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
- Big 집합인 emp를 풀스캔 하는 대신 인덱스만 스캔하여 해시테이블 빌드
- Build Input의 크기를 줄임으로써 Disk IO를 최소화 하려는 아이디어
- 아우터 해시조인 완성후 emp의 자료를 조회하기 위해 다시 셀프 조인이 필요
- Disk IO 는 줄었지만 해시버킷당 엔트리 갯수가 많아서 생기는 문제는 피할 수 없다.
- 책의 예제에서는 주문일시 구간을 나누어 쿼리를 여러번 수행하는 해결책을 제시한다.
- 주문테이블은 주문일시로 Range 파티셔닝 되어 있을 것이고
일정한 주문일시 구간내의 고객수는 많지 않을 것이므로
해시버킷당 엔트리 갯수가 많아서 생기는 문제를 최소화 할 수 있다.
고객테이블을 반복적으로 읽는 ?율에도 불구하고 빠르게 수행될 것으로 예상
(4) Full Outer 조인
테스트 테이블 생성
SQL> EXEC DBMS_RANDOM.SEED(150);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> CREATE TABLE 입금
2 AS
3 SELECT ROWNUM 일련번호
4 , ROUND(DBMS_RANDOM.VALUE(1, 20)) 고객ID
5 , ROUND(DBMS_RANDOM.VALUE(1000, 1000000), -2) 입금액
6 FROM dual CONNECT BY LEVEL <= 10
7 ;
테이블이 생성되었습니다.
SQL>
SQL> CREATE TABLE 출금
2 AS
3 SELECT ROWNUM 일련번호
4 , ROUND(DBMS_RANDOM.VALUE(1, 20)) 고객ID
5 , ROUND(DBMS_RANDOM.VALUE(1000, 1000000), -2) 입금액
6 FROM dual CONNECT BY LEVEL <= 10
7 ;
테이블이 생성되었습니다.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, '입금');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, '출금');
PL/SQL 처리가 정상적으로 완료되었습니다.
'Left Outer 조인 + Union All + Anti 조인(Not Exists 필터)' 이용
Left Outer 조인 + Union All + Anti 조인(Not Exists 필터)
SQL> set autot on exp
SQL> set pagesize 20
SQL>
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 a.고객ID, Null, a.출금액
7 FROM (SELECT 고객ID, SUM(출금액) 출금액 FROM 출금 GROUP BY 고객ID) a
8 WHERE NOT EXISTS (SELECT 'x' FROM 입금 WHERE 고객ID = a.고객ID)
9 ;
고객ID 입금액 출금액 결과를 통해 처리과정을 유추해 보자.
---------- ---------- ---------- --------------------------------------------------
6 707000 342900 ┐ 1. 입금 테이블을 해시테이블로 빌드
3 259400 768100 ├ 2. 출금테이블을 스캔하면서 해시테이블 탐색(Probe)
19 398300 558800 ┘ 3. 조인에 성공한 자료를 결과집합에 삽입
13 59400 ┐
8 957000 │
1 224100 ├ 4. 해시테이블에서 조인에 성공하지 못한 집합
2 231900 │
18 336900 │
4 1211700 ┘
11 599500 ┐
20 193300 │
5 264500 │
15 299400 ├ 5. 출금테이블 자료중 입금에 없는 자료 추가
12 958200 │
10 205700 │
9 18100 ┘
16 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3598059211
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 478 | 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 | | 10 | 260 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 10 | 70 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| 출금 | 10 | 70 | 3 (0)| 00:00:01 |
| 9 | HASH GROUP BY | | 1 | 10 | 8 (25)| 00:00:01 |
|* 10 | HASH JOIN ANTI | | 1 | 10 | 7 (15)| 00:00:01 |
| 11 | TABLE ACCESS FULL | 출금 | 10 | 70 | 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 조인
Ansi Full Outer 조인
SQL> SELECT NVL(a.고객ID, b.고객ID) 고객ID
2 , 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
7 ;
고객ID 입금액 출금액
---------- ---------- ----------
6 707000 342900
3 259400 768100
19 398300 558800
13 59400
8 957000
1 224100
2 231900
18 336900
4 1211700
11 599500
20 193300
5 264500
10 205700
12 958200
9 18100
15 299400
16 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 82146744
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 936 | 28 (15)| 00:00:01 |
| 1 | VIEW | | 18 | 936 | 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 | | 10 | 300 | 4 (25)| 00:00:01 |
| 8 | HASH GROUP BY | | 10 | 70 | 4 (25)| 00:00:01 |
| 9 | TABLE ACCESS FULL | 출금 | 10 | 70 | 3 (0)| 00:00:01 |
| 10 | HASH GROUP BY | | 9 | 63 | 19 (6)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | TABLE ACCESS FULL | 출금 | 10 | 70 | 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 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."고객ID"="B"."고객ID"(+))
11 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "입금" "입금" WHERE
"고객ID"=:B1 GROUP BY "고객ID"))
14 - filter("고객ID"=:B1)
Native Hash Full Outer 조인
- 오라클 11g에서 선보임
- 10.2.0.4버전에서 Hidden 파라미터 조정으로 이 기능 사용 가능
Native Hash Full Outer 조인
SELECT /*+ opt_param('_optimizer_native_full_outer_join', 'force') */
NVL(a.고객ID, b.고객ID) 고객ID
, a.입금액, b.출금액
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 입금액 출금액 결과를 통해 처리과정을 유추해 보자.
---------- ---------- ---------- --------------------------------------------------
6 707000 342900 ┐
13 59400 │
8 957000 │
1 224100 ├ 1. 출금 테이블을 해시테이블로 빌드
3 259400 768100 │ 2. 입금테이블을 스캔하면서 해시테이블 탐색(Probe)
19 398300 558800 │ 3. 조인 성공과 상관없이 모두 결과집합에 삽입
2 231900 │
18 336900 │
4 1211700 ┘
11 599500 ┐
20 193300 │
5 264500 │
10 205700 ├ 4. 해시테이블에서 조인에 성공하지 못한 집합
12 958200 │
9 18100 │
15 299400 ┘
16 개의 행이 선택되었습니다.
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes| Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | | | | 00:00:01 |
| 2 | HASH JOIN FULL OUTER | | | | | 00:00:01 |
| 3 | VIEW | | | | | 00:00:01 |
| 4 | HASH GROUP BY | | | | | 00:00:01 |
| 5 | TABLE ACCESS FULL | 입금 | | | | 00:00:01 |
| 6 | VIEW | | | | | 00:00:01 |
| 7 | HASH GROUP BY | | | | | 00:00:01 |
| 8 | TABLE ACCESS FULL | 출금 | | | | 00:00:01 |
---------------------------------------------------------------------------------
Union all 을 이용한 Full Outer 조인
Union all 을 이용한 Full Outer 조인
SQL> SELECT 고객ID
2 , SUM(입금액) 입금액
3 , SUM(출금액) 출금액
4 FROM (SELECT 고객ID, 입금액, TO_NUMBER(null) 출금액 FROM 입금
5 UNION ALL
6 SELECT 고객ID, TO_NUMBER(null) 입금액, 출금액 FROM 출금
7 )
8 GROUP BY 고객ID
9 ;
고객ID 입금액 출금액
---------- ---------- ----------
1 224100
6 707000 342900
13 59400
11 599500
2 231900
20 193300
4 1211700
5 264500
8 957000
3 259400 768100
18 336900
19 398300 558800
10 205700
12 958200
9 18100
15 299400
16 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 2888689472
-----------------------------------------------------------------------------
| 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| 입금 | 10 | 70 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| 출금 | 10 | 70 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------