(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 | -----------------------------------------------------------------------------
===== 아래와 같이 출처를 표기합니다. =====================================================================
'ORACLE > SQL' 카테고리의 다른 글
잡동사니 퀴리 (0) | 2017.07.05 |
---|---|
DML 수행 시 내부 절차 (0) | 2017.06.20 |
관리를 위한 쿼리 모음 (0) | 2017.06.16 |
Oracle 스크립트 모음 (0) | 2017.06.16 |
병렬 DML 모니터링 방법 (0) | 2017.06.15 |