by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

5. Outer 조인




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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.