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

5. Outer 조인




I. Outer NL 조인

1. Outer NL 조인은 단방향 액세스 패턴을 가짐

  • Outer NL 조인은 특성상 기준 테이블에서 Outer 대상 테이블로만 가는 단방향 액세스패턴을 가질 수 밖에 없음
  • 단방향 액세스밖에 안되는 이유
    
    -- 1. EMP, DEPT 데이터 구조
    SELECT D.DEPTNO,
           COUNT(E.DEPTNO) EMP_CNT,
           COUNT(D.DEPTNO) DEPT_CNT
    FROM   EMP  E,
           DEPT D
    WHERE  E.DEPTNO(+) = D.DEPTNO
    GROUP  BY D.DEPTNO
    ORDER  BY D.DEPTNO
    ;
    
        DEPTNO    EMP_CNT   DEPT_CNT
    ---------- ---------- ----------
            10          9          9
            20         90         90
            30        900        900
            40       9000       9000
            50      90000      90000
            60          1          1
            70          0          1
            80          0          1
            90          0          1
    ;
    
    -- 2. Outer 조인 결과
    SELECT *
    FROM   EMP  E,
           DEPT D
    WHERE  E.DEPTNO(+) = D.DEPTNO
    AND    D.DEPTNO IN (60, 70, 80, 90)
    ;
    
         EMPNO EMPNO_VARCHAR JOB    HIREDATE         DEPTNO     DEPTNO DNAME     
    ---------- ------------- ------ ------------ ---------- ---------- --------
        100000 100000        A      18-MAY-28            60         60 SALES_6   
                                                                    80 SALES_8   
                                                                    90 SALES_9   
                                                                    70 SALES_7   
    
  • 조인결과를 보면 EMP 테이블에 3건의 NULL 레코드가 보인다.
    이 때 Outer NL 조인을 하려면 드라이빙 테이블에 데이터가 있어야 Outer 여부를 확인할 수 있는데
    EMP 테이블에는 이미 NULL 값 밖에 없기 때문에 이 NULL 값을 가지고 DEPT 테이블의 데이터와
    비교를 할 수 없으므로 항상 데이터가 존재하는 DEPT 테이블만 드라이빙이 될 수 밖에 없다.
  • 실행계획 확인
    
    -- 0. 오라클 버전 
    SELECT * FROM v$version;
    
    BANNER                                                               
    ---------------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit     
    PL/SQL Release 10.2.0.3.0 - Production                               
    
    -- 1. Outer NL 조인
    EXPLAIN PLAN FOR 
    SELECT /*+ USE_NL(D E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |        |   100K|  4003K|   702   (1)| 00:00:09 |
    |   1 |  NESTED LOOPS OUTER          |        |   100K|  4003K|   702   (1)| 00:00:09 |
    |   2 |   TABLE ACCESS FULL          | DEPT   |     9 |   162 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS BY INDEX ROWID| EMP    | 11111 |   249K|   134   (1)| 00:00:02 |
    |*  4 |    INDEX RANGE SCAN          | EMP_N1 | 25000 |       |    33   (4)| 00:00:01 |
    ---------------------------------------------------------------------------------------
                                                                                           
    Outline Data                                                                           
    -------------                                                                          
                                                                                           
      /*+                                                                                  
          BEGIN_OUTLINE_DATA                                                               
          USE_NL(@"SEL$1" "E"@"SEL$1")                                                     
          LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")                                        
          INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))                              
          FULL(@"SEL$1" "D"@"SEL$1")                                                       
          OUTLINE_LEAF(@"SEL$1")                                                           
          ALL_ROWS                                                                         
          OPT_PARAM('_optim_peek_user_binds' 'false')                                      
          OPT_PARAM('_fast_full_scan_enabled' 'false')                                     
          OPT_PARAM('_b_tree_bitmap_plans' 'false')                                        
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                            
          IGNORE_OPTIM_EMBEDDED_HINTS                                                      
          END_OUTLINE_DATA                                                                 
      */                                                                                   
                                                                                           
    Predicate Information (identified by operation id):                                    
    ---------------------------------------------------                                    
                                                                                           
       4 - access("D"."DEPTNO"="E"."DEPTNO"(+))  
       
    -- 2. LEADING 힌트로 E -> D 유도 
    -- E -> D로 실행계획 안됨
    EXPLAIN PLAN FOR 
    SELECT /*+ LEADING(E D) USE_NL(D E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
    
    --------------------------------------------------------------------------------------- 
    | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | 
    --------------------------------------------------------------------------------------- 
    |   0 | SELECT STATEMENT             |        |   100K|  4003K|   702   (1)| 00:00:09 | 
    |   1 |  NESTED LOOPS OUTER          |        |   100K|  4003K|   702   (1)| 00:00:09 | 
    |   2 |   TABLE ACCESS FULL          | DEPT   |     9 |   162 |     3   (0)| 00:00:01 | 
    |   3 |   TABLE ACCESS BY INDEX ROWID| EMP    | 11111 |   249K|   134   (1)| 00:00:02 | 
    |*  4 |    INDEX RANGE SCAN          | EMP_N1 | 25000 |       |    33   (4)| 00:00:01 | 
    --------------------------------------------------------------------------------------- 
                                                                                            
    Outline Data                                                                            
    -------------                                                                           
                                                                                            
      /*+                                                                                   
          BEGIN_OUTLINE_DATA                                                                
          USE_NL(@"SEL$1" "E"@"SEL$1")                                                      
          LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")                                         
          INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))                               
          FULL(@"SEL$1" "D"@"SEL$1")                                                        
          OUTLINE_LEAF(@"SEL$1")                                                            
          ALL_ROWS                                                                          
          OPT_PARAM('_optim_peek_user_binds' 'false')                                       
          OPT_PARAM('_fast_full_scan_enabled' 'false')                                      
          OPT_PARAM('_b_tree_bitmap_plans' 'false')                                         
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                             
          IGNORE_OPTIM_EMBEDDED_HINTS                                                       
          END_OUTLINE_DATA                                                                  
      */                                                                                    
                                                                                            
    Predicate Information (identified by operation id):                                     
    ---------------------------------------------------                                     
                                                                                            
       4 - access("D"."DEPTNO"="E"."DEPTNO"(+))  
       
    -- 3. LEADING 힌트보다 강력한 ORDERED 힌트로 E -> D 유도 
    -- E -> D로 실행계획 안됨
    EXPLAIN PLAN FOR 
    SELECT /*+ ORDERED USE_NL(D E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |        |   100K|  4003K|   702   (1)| 00:00:09 |
    |   1 |  NESTED LOOPS OUTER          |        |   100K|  4003K|   702   (1)| 00:00:09 |
    |   2 |   TABLE ACCESS FULL          | DEPT   |     9 |   162 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS BY INDEX ROWID| EMP    | 11111 |   249K|   134   (1)| 00:00:02 |
    |*  4 |    INDEX RANGE SCAN          | EMP_N1 | 25000 |       |    33   (4)| 00:00:01 |
    ---------------------------------------------------------------------------------------
                                                                                           
    Outline Data                                                                           
    -------------                                                                          
                                                                                           
      /*+                                                                                  
          BEGIN_OUTLINE_DATA                                                               
          USE_NL(@"SEL$1" "E"@"SEL$1")                                                     
          LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")                                        
          INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))                              
          FULL(@"SEL$1" "D"@"SEL$1")                                                       
          OUTLINE_LEAF(@"SEL$1")                                                           
          ALL_ROWS                                                                         
          OPT_PARAM('_optim_peek_user_binds' 'false')                                      
          OPT_PARAM('_fast_full_scan_enabled' 'false')                                     
          OPT_PARAM('_b_tree_bitmap_plans' 'false')                                        
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                            
          IGNORE_OPTIM_EMBEDDED_HINTS                                                      
          END_OUTLINE_DATA                                                                 
      */                                                                                   
                                                                                           
    Predicate Information (identified by operation id):                                    
    ---------------------------------------------------                                    
                                                                                           
       4 - access("D"."DEPTNO"="E"."DEPTNO"(+)) 
    

2. 데이터 형변환 조인도 액세스 단방향 패턴

  • Outer NL 조인 뿐만 아니라 데이터 형변환을 통해 조인이 된 경우에도 단방향 액세스 패턴과 같다고 볼 수 있음
    
    -- 1. 이 경우는 형변환 안한 테이블에 조건이 들어와 형변환 한곳으로 넘어가야 하는데 인덱스가 없으므로 FULL SCAN함
    EXPLAIN PLAN FOR
    SELECT /*+ USE_NL(E_1 E_2) */
           *
    FROM   EMP  E_1,
           EMP  E_2
    WHERE  E_1.EMPNO = TO_NUMBER(E_2.EMPNO_VARCHAR)
    AND    E_1.HIREDATE BETWEEN :1 AND :2
    --AND    E_2.HIREDATE BETWEEN :1 AND :2
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |        |   250 | 11500 | 28606   (3)| 00:05:44 |
    |*  1 |  FILTER                       |        |       |       |            |          |
    |   2 |   NESTED LOOPS                |        |   250 | 11500 | 28606   (3)| 00:05:44 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |   250 |  5750 |     5   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | EMP_N2 |   450 |       |     3   (0)| 00:00:01 |
    |*  5 |    TABLE ACCESS FULL          | EMP    |     1 |    23 |   114   (2)| 00:00:02 |
    ----------------------------------------------------------------------------------------
                                                                                            
    Predicate Information (identified by operation id):                                     
    ---------------------------------------------------                                     
                                                                                            
       1 - filter(TO_DATE(:1)<=TO_DATE(:2))                                                 
       4 - access("E_1"."HIREDATE">=:1 AND "E_1"."HIREDATE"<=:2)                            
       5 - filter("E_1"."EMPNO"=TO_NUMBER("E_2"."EMPNO_VARCHAR"))                           
       
    -- 2. 이 경우는 형변환 한 테이블에 조건이 들어와 형변환 안한곳으로 넘어가는데 인덱스가 있으므로 INDEX SCAN함
    EXPLAIN PLAN FOR
    SELECT /*+ USE_NL(E_1 E_2) */
           *
    FROM   EMP  E_1,
           EMP  E_2
    WHERE  E_1.EMPNO = TO_NUMBER(E_2.EMPNO_VARCHAR)
    --AND    E_1.HIREDATE BETWEEN :1 AND :2
    AND    E_2.HIREDATE BETWEEN :1 AND :2
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |        |   250 | 11500 |   255   (0)| 00:00:04 |
    |*  1 |  FILTER                       |        |       |       |            |          |
    |   2 |   NESTED LOOPS                |        |   250 | 11500 |   255   (0)| 00:00:04 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |   250 |  5750 |     5   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | EMP_N2 |   450 |       |     3   (0)| 00:00:01 |
    |   5 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    23 |     1   (0)| 00:00:01 |
    |*  6 |     INDEX UNIQUE SCAN         | EMP_U1 |     1 |       |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
                                                                                            
    Predicate Information (identified by operation id):                                     
    ---------------------------------------------------                                     
                                                                                            
       1 - filter(TO_DATE(:1)<=TO_DATE(:2))                                                 
       4 - access("E_2"."HIREDATE">=:1 AND "E_2"."HIREDATE"<=:2)                            
       6 - access("E_1"."EMPNO"=TO_NUMBER("E_2"."EMPNO_VARCHAR"))                                 
    

3. 데이터 형변환 조인을 양방향 액세스 패턴으로 변경하는 방법

  • 위의 내용처럼 형변환을 통해 조인한 경우 단방향 액세스 패턴과 같은 현상을 보이는데
    이를 극복하기 위해 양쪽에 모두 형변환을 해주어 양방향 패턴이 가능하도록 해주면 해결 가능
  • 단 양쪽 조인컬럼 모두 인덱스가 있을 경우임!!!
    EXPLAIN PLAN FOR
    SELECT /*+ USE_NL(E_1 E_2) */
           *
    FROM   EMP  E_1,
           EMP  E_2
    WHERE  E_1.EMPNO          = TO_NUMBER(E_2.EMPNO_VARCHAR)
    AND    TO_CHAR(E_1.EMPNO) = E_2.EMPNO_VARCHAR -- 조건 추가 
    AND    E_1.HIREDATE       BETWEEN :1 AND :2
    --AND    E_2.HIREDATE BETWEEN :1 AND :2
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |        |   250 | 11500 |   505   (0)| 00:00:07 |
    |*  1 |  FILTER                        |        |       |       |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID  | EMP    |     1 |    23 |     2   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS                |        |   250 | 11500 |   505   (0)| 00:00:07 |
    |   4 |     TABLE ACCESS BY INDEX ROWID| EMP    |   250 |  5750 |     5   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | EMP_N2 |   450 |       |     3   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN           | EMP_N3 |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
                                                                                             
    Predicate Information (identified by operation id):                                      
    ---------------------------------------------------                                      
                                                                                             
       1 - filter(TO_DATE(:1)<=TO_DATE(:2))                                                  
       5 - access("E_1"."HIREDATE">=:1 AND "E_1"."HIREDATE"<=:2)                             
       6 - access("E_2"."EMPNO_VARCHAR"=TO_CHAR("E_1"."EMPNO"))                              
           filter("E_1"."EMPNO"=TO_NUMBER("E_2"."EMPNO_VARCHAR")) 
    

II. Outer 소트 머지 조인

Outer 소트 머지 조인도 Outer NL 조인과 마찬가지로 단방향 액세스

EXPLAIN PLAN FOR 
SELECT /*+ ORDERED USE_MERGE(D E) */
       *
FROM   EMP  E,
       DEPT D
WHERE  D.DEPTNO = E.DEPTNO(+)
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   100K|  4003K|       |   811   (2)| 00:00:10 |
|   1 |  MERGE JOIN OUTER   |      |   100K|  4003K|       |   811   (2)| 00:00:10 |
|   2 |   SORT JOIN         |      |     9 |   162 |       |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     9 |   162 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100K|  2246K|  7864K|   807   (2)| 00:00:10 |
|   5 |    TABLE ACCESS FULL| EMP  |   100K|  2246K|       |   117   (3)| 00:00:02 |
------------------------------------------------------------------------------------
                                                                                    
Outline Data                                                                        
-------------                                                                       
                                                                                    
  /*+                                                                               
      BEGIN_OUTLINE_DATA                                                            
      USE_MERGE(@"SEL$1" "E"@"SEL$1")                                               
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")                                     
      FULL(@"SEL$1" "E"@"SEL$1")                                                    
      FULL(@"SEL$1" "D"@"SEL$1")                                                    
      OUTLINE_LEAF(@"SEL$1")                                                        
      ALL_ROWS                                                                      
      OPT_PARAM('_optim_peek_user_binds' 'false')                                   
      OPT_PARAM('_fast_full_scan_enabled' 'false')                                  
      OPT_PARAM('_b_tree_bitmap_plans' 'false')                                     
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                         
      IGNORE_OPTIM_EMBEDDED_HINTS                                                   
      END_OUTLINE_DATA                                                              
  */                                                                                
                                                                                    
Predicate Information (identified by operation id):                                 
---------------------------------------------------                                 
                                                                                    
   4 - access("D"."DEPTNO"="E"."DEPTNO"(+))                                         
       filter("D"."DEPTNO"="E"."DEPTNO"(+))                                         

III. Outer 해시 조인

1. 소개

  • Outer 해시 조인도 9i까지는 단방향 액세스 패턴이었으나, 10g에서 성능상의 이유로 변경됨
    
    -- 1. 오라클 버전
    SELECT * FROM v$version;
    
    BANNER                                                            
    ------------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit  
    PL/SQL Release 10.2.0.3.0 - Production                            
    
    -- 2. 세션 레벨에서 오라클 버전 9i로 변경
    ALTER SESSION SET optimizer_features_enable = '9.2.0';
    
    -- 3. 실행계획 확인
    EXPLAIN PLAN FOR
    SELECT /*+ USE_HASH(D E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
    
    -----------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost  |
    -----------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |   100K|  4003K|    67 |
    |*  1 |  HASH JOIN OUTER   |      |   100K|  4003K|    67 |
    |   2 |   TABLE ACCESS FULL| DEPT |     9 |   162 |     2 |
    |   3 |   TABLE ACCESS FULL| EMP  |   100K|  2246K|    64 |
    -----------------------------------------------------------
                                                               
    Outline Data                                               
    -------------                                              
                                                               
      /*+                                                      
          BEGIN_OUTLINE_DATA                                   
          USE_HASH(@"SEL$1" "E"@"SEL$1")                       
          LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")            
          FULL(@"SEL$1" "E"@"SEL$1")                           
          FULL(@"SEL$1" "D"@"SEL$1")                           
          OUTLINE_LEAF(@"SEL$1")                               
          OPT_PARAM('_optim_peek_user_binds' 'false')          
          OPT_PARAM('_fast_full_scan_enabled' 'false')         
          OPT_PARAM('_b_tree_bitmap_plans' 'false')            
          OPTIMIZER_FEATURES_ENABLE('9.2.0')                   
          IGNORE_OPTIM_EMBEDDED_HINTS                          
          END_OUTLINE_DATA                                     
      */                                                       
                                                               
    Predicate Information (identified by operation id):        
    ---------------------------------------------------        
                                                               
       1 - access("D"."DEPTNO"="E"."DEPTNO"(+))                
                                                               
    Note                                                       
    -----                                                      
       - cpu costing is off (consider enabling it)             
    ;
    
    -- 4. 10g로 변경
    ALTER SESSION SET optimizer_features_enable = '10.2.0.3';
    
    -- 5. 다시 확인
    -- 책의 내용이 일부 잘못된 것으로 보임. 
    -- SQL 내용 변경 없이 SWAP_JOIN_INPUTS(D) -> SWAP_JOIN_INPUTS(E)로 변경해야 함
    EXPLAIN PLAN FOR
    SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));
    
    ------------------------------------------------------------------------------
    | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |    14 |   798 |    11  (10)| 00:00:01 |
    |*  1 |  HASH JOIN RIGHT OUTER|      |    14 |   798 |    11  (10)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL   | EMP  |    14 |   518 |     5   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL   | DEPT |     4 |    80 |     5   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
                                                                                  
    Outline Data                                                                  
    -------------                                                                 
                                                                                  
      /*+                                                                         
          BEGIN_OUTLINE_DATA                                                      
          SWAP_JOIN_INPUTS(@"SEL$1" "E"@"SEL$1")                                  
          USE_HASH(@"SEL$1" "E"@"SEL$1")                                          
          LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")                               
          FULL(@"SEL$1" "E"@"SEL$1")                                              
          FULL(@"SEL$1" "D"@"SEL$1")                                              
          OUTLINE_LEAF(@"SEL$1")                                                  
          ALL_ROWS                                                                
          OPT_PARAM('_optim_peek_user_binds' 'false')                             
          OPT_PARAM('_fast_full_scan_enabled' 'false')                            
          OPT_PARAM('_b_tree_bitmap_plans' 'false')                               
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                   
          IGNORE_OPTIM_EMBEDDED_HINTS                                             
          END_OUTLINE_DATA                                                        
      */                                                                          
                                                                                  
    Predicate Information (identified by operation id):                           
    ---------------------------------------------------                           
                                                                                  
       1 - access("D"."DEPTNO"="E"."DEPTNO"(+))                                                                    
    
  • 9i에의 해시 조인 알고리즘
    • Outer 집합인 DEPT 테이블을 해시 테이블로 빌드(Build)함
    • Inner 집합인 EMP 테이블을 읽으면서 해시 테이블을 탐색(Probe)
    • 조인에 성공한 레코드는 곧바로 결과집합에 삽입하고, 조인에 성공했음을 해시 엔트리에 표시(그림_1)
    • Probe 단계가 끝나면 Inner 조인과 동일한 결과집합이 만들어진 상태.
      이제 조인에 실패했던 레코드를 결과집합에 포함시켜야 하므로 해시 테이블을 스캔하면서
      체크가 없는 DEPT 엔트리를 결과집합에 삽입한다(그림_2)
  • 그림_1
  • 그림_2
  • 하지만 일반적으로 해시 조인을 할 때 집합이 작은 대상이 Outer가 되어야 성능에 좋음에도 불구하고
    집합이 큰 대상이 Outer가 되는 경우가 종종 있어 성능에 치명적인 영향을 주게 되었다.
  • 이에 오라클은 10g에서 Outer가 아닌 Inner 또한 빌드가 가능하도록 추가한 것이 Right Outer 해시 조인이다.

2. 의문사항(논의 사항)

  • 즉, Right Outer 해시 조인 탄생 배경은 집합이 작은 대상을 빌드로 만드는 것이 목적인데 실제 Trace를 보면 Block을 더 읽는다. _
    왜 그럴까?
    
    -- 1. Normal Outer Hash Join
    /*
    USE_HASH(@"SEL$1" "E"@"SEL$1")           
    LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
    */
    
    SELECT /*+ USE_HASH(D E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.000          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch    10002    0.000        0.440          0      10363          0     100003
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total    10004    0.000        0.440          0      10363          0     100003
    
    Misses in library cache during parse: 0
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
     100003   HASH JOIN OUTER (cr=10363 pr=0 pw=0 time=201178 us)
          9    TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=130 us)
     100000    TABLE ACCESS FULL EMP (cr=10360 pr=0 pw=0 time=69 us)
    
    
    -- 2. Right Outer Hash Join
    /*
    SWAP_JOIN_INPUTS(@"SEL$1" "E"@"SEL$1")    
    USE_HASH(@"SEL$1" "E"@"SEL$1")            
    LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1") 
    */
    
    SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.003          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch    10002    0.170        0.440          0        413          0     100003
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total    10004    0.170        0.443          0        413          0     100003
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
     100003   HASH JOIN RIGHT OUTER (cr=413 pr=0 pw=0 time=265771 us)
     100000    TABLE ACCESS FULL EMP (cr=404 pr=0 pw=0 time=112 us)
          9    TABLE ACCESS FULL DEPT (cr=9 pr=0 pw=0 time=139 us)
    
  • 보는 것처럼 여기서 'Right Outer Hash Join'은 'SWAP_JOIN_INPUTS(E)'으로 인해 EMP를
    빌드하고 DEPT를 Probe 하였는데 I/O는 10,363 -> 413로 줄어들었다.(1/25 감소)
  • 여기서 의문사항 2가지
    • 'Right Outer Hash Join'의 진정한 의미는 작은 집합이 아닌 큰 집합을 빌드하는 것이 효과적인 것인가?
    • 트레이스 실행계획은 'E->D'로 풀렸는데 Outline 힌트에 보면 'LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")'이다.
      이는 무엇으로 설명을 해야 하나?
  • 이에 대해 비투엔에서 답변한 내용
  • 질문 : http://www.b2en.com/comm/performance/view.asp?sfield=&svalue=&Page=&pageSize=&no=84
    제목 : bulid input을 큰 집합으로 할때 성능이 더 좋은 case가 있어 추가 질문 드립니다.
    
    안녕하세요? 조시형 선생님.
    
    역시나, 작은 쪽 테이블을 bulid input으로 선택하기 위해 그렇게 표현을 한 것이군요.
    답변 감사드립니다.
    
    그런데 제가 한번 emp테이블에 데이터를 많이 넣고 테스트를 해보았는데 emp테이블을 bulid input으로 
    한 결과가 더 좋게 나온게 있어 이에 대해 문의를 한번 더 드립니다.
    
    아래의 스크립트에서 dept는 9건, emp는 10만건을 넣어 본 뒤 dept와 emp를 각각 bulid input하여 테스트를 
    해 보았습니다.
    
    -- 1. oracle version
    SELECT * FROM v$version;
    
    BANNER                                                              
    --------------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit    
    PL/SQL Release 10.2.0.3.0 - Production                              
    
    -- 2. create table
    DROP TABLE EMP PURGE;
    DROP TABLE DEPT PURGE;
    
    CREATE TABLE EMP AS
    SELECT -- 1-1) EMPNO(Unique 컬럼)
           LEVEL EMPNO,
           -- 1-2) EMPNO(VARCHAR로 변형)
           TO_CHAR(LEVEL) EMPNO_VARCHAR,
           -- 1-3) JOB(10만건 데이터를 20개 그룹으로 분류)
           CHR(65 + CEIL(LEVEL / 500000) - 1) JOB,
           -- 1-4) HIREDATE(10개씩 증가하여 날짜 데이터 만듬)  
           TO_DATE('00010101', 'YYYYMMDD') + CEIL(LEVEL / 10) - 1 HIREDATE,
           -- 1-5) DEPTNO(자리수 길이를 한 그룹으로 하여 데이터 생성, 분포도 차이나도록) 
           LENGTH(LEVEL) * 10 DEPTNO
    FROM   DUAL
    CONNECT BY LEVEL <= 100000
    ;
    
    CREATE TABLE DEPT AS
    SELECT LEVEL * 10        DEPTNO,
           'SALES_' || LEVEL DNAME,
           'ZONE_'  || LEVEL LOC
    FROM   DUAL
    CONNECT BY LEVEL <= 9
    ;
    
    -- 3. create index
    CREATE UNIQUE INDEX EMP_U1 ON EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX EMP_U1 NOPARALLEL;
    
    CREATE INDEX EMP_N1 ON EMP (DEPTNO) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX EMP_N1 NOPARALLEL;
    
    CREATE INDEX EMP_N2 ON EMP (HIREDATE) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX EMP_N2 NOPARALLEL;
    
    CREATE INDEX EMP_N3 ON EMP (EMPNO_VARCHAR) COMPUTE STATISTICS PARALLEL 8;
    ALTER INDEX EMP_N3 NOPARALLEL;
    
    CREATE UNIQUE INDEX DEPT_U1 ON DEPT (DEPTNO);
    
    -- 4. gather statistics
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);
    
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);
    
    -- 5. dept bulid input 
    SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(D) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.003          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch    10002    0.010        0.681          0      10363          0     100003
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total    10004    0.010        0.685          0      10363          0     100003
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
     100003   HASH JOIN OUTER (cr=10363 pr=0 pw=0 time=212801 us)
          9    TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=11638 us)
     100000    TABLE ACCESS FULL EMP (cr=10360 pr=0 pw=0 time=97 us)
    
    -- 6. emp bulid input
    SELECT /*+ USE_HASH(D E) SWAP_JOIN_INPUTS(E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.003          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch    10002    0.160        0.559          0        413          0     100003
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total    10004    0.160        0.562          0        413          0     100003
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
     100003   HASH JOIN RIGHT OUTER (cr=413 pr=0 pw=0 time=267378 us)
     100000    TABLE ACCESS FULL EMP (cr=404 pr=0 pw=0 time=112 us)
          9    TABLE ACCESS FULL DEPT (cr=9 pr=0 pw=0 time=150 us)
    ;
    
    보시는 것처럼 dept를 bulid input할 때 emp를 10,360블록 액세스한 반면, emp를 bulid input할 때는 emp를 
    404블록만 액세스한것이 보입니다.
    
    이렇게 데이터가 많은 emp를 bulid input한 결과가 더 좋게 나온 이유는 왜 그런것일까요?
    책에서 소개해 주신것처럼 bulid input한 테이블은 가급적 집합이 작은 테이블을 만드는것이 유리하다고 
    하셨는데 말이죠. 제 추측으로는 집합이 큰 테이블이라 하더라도 조인키의 NDV 값이 작다면 bulid input할 때 
    해시 버킷을 적절히 나눌 수 있어 좋을것 같다고 예측만 될 뿐인데...
    
    조시형 선생님께서는 어떻게 생각하시는지 궁금합니다.
    
  • 답변 : http://www.b2en.com/comm/performance/view.asp?sfield=&svalue=&Page=&pageSize=&no=85
    답변이 늦어져 죄송합니다. 회사 사정상 늘 프로젝트를 병행하다 보니 
    프로젝트 일정이 바쁠 때는 빨리 답변하기 곤란할 때가 잦습니다. 양해 바랍니다.
    
    올려주신 스크립트를 간단히 살펴봤는데, 원인은 해시 조인 메커니즘(작은 테이블을 Build Input으로 삼아야 
    빠르다)에 있지 않고, 다량의 데이터베이스 Call 때문에 블록 읽기가 증가한 데에 있습니다. dept를 
    Build Input으로 삼았을 때는 블록 I/O가 10,363이고, emp를 Build Input으로 삼았을 때는 413입니다. 
    왜 이런 차이가 발생할까요? 
    
    이 원리를 이해하시려면 오라클 성능 고도화 1권 5장에 설명한 ′데이터베이스 Call 최소화 원리′를 먼저 
    이해하셔야 합니다. 그중에서도, 1권 5장 5절 (3)항(381~385 페이지)을 참조하시기 바랍니다. 
    
    해당 내용을 읽기 전에 먼저 아래 테스트를 수행해 보시죠.
    
    1. select-list를 ′*′로 하지 마시고 아래와 같이 바꿔 보세요. 
    
    select count(dept_nullable_column), count(emp_nullable_column) from ...
    
    그러면 데이터베이스 Call이 한 번만 발생하면서 그 Call 내에서 모든 조인 처리를 완료하겠죠.
    그러면 데이터베이스 Call 횟수에 영향을 받지 않고 순수하게 해시 조인 메커니즘에 의한 성능 차이를 
    비교할 수 있습니다. 
    
    2. Array Size를 5,000쯤으로 증가시켜 테스트해 보는 것도 방법입니다. 현재 Fetch Call이 10,004번 
    발생했는데, Array Size를 5,000으로 증가시키면 불과 2~3번의 Fetch Call이 발생할 것입니다. 
    그러면 Fetch Call에 의한 블록 I/O 증가량은 아주 미미할 테니, 순수하게 해시 조인 메커니즘 성능 
    차이를 비교할 수 있습니다.
    
    3. 또는 양쪽 쿼리모두 emp 쪽 테이블을 아래와 같이 order by가 발생하도록 인라인 뷰로 씌웁니다.
    
    from (select rownum, emp.* from EMP order by empno) e, dept d
    
    그러면 Fetch Call이 다량 발생하더라도 Sort Area에서 액세스하므로 추가적인 CR 읽기가 발생하지 않습니다. 
    
    위 테스트를 모두 수행해 보고 1권 5장 5절에 설명한 원리를 충분히 이해하신다면, 테스트 결과를 어렵지 
    않게 해석할 수 있습니다. 그리고 해시 조인할 때 작은 집합을 Build Input으로 삼아야 유리하다는 원리에도 
    변함이 없음을 이해하실 것입니다. 
    
    참고로 이와 관련된 내용을 이미 설명해 놓은 부분이 있는데, 2권 p131 상단 첫 번째 문단을 참조하시기 바랍니다. 
    질문하신 예제에선, 책의 예제처럼 Fetch Call마다 반복적인 클러스터 스캔이 발생하지 않는다는 점만 다를 뿐, 
    작은 쪽 테이블을 Build Input으로 삼았는데 오히려 더 느려지는 현상은 동일하므로 이해하시는 데 도움이 되실 겁니다. 
    
    답변이 되셨기를 바랍니다.
    
    조시형 드림
    
  • 테스트 결과
    
    -- 1. 테스트_1
    SELECT /*+ USE_HASH(D E) */
           count(e.hiredate), count(d.dname)
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.010        0.003          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        2    0.150        0.846        402        407          0          1
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        4    0.160        0.849        402        407          0          1
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
          1   SORT AGGREGATE (cr=407 pr=402 pw=0 time=845766 us)
     100003    HASH JOIN OUTER (cr=407 pr=402 pw=0 time=243128 us)
          9     TABLE ACCESS FULL DEPT (cr=3 pr=2 pw=0 time=12719 us)
     100000     TABLE ACCESS FULL EMP (cr=404 pr=400 pw=0 time=29205 us)
    
    -- 2. 테스트_2
    set arraysize 5000
    
    SELECT /*+ USE_HASH(D E) */
           *
    FROM   EMP  E,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       22      0.23       0.93        402        427          0      100003
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       24      0.23       0.93        402        427          0      100003
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 44  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
     100003  HASH JOIN OUTER (cr=427 pr=402 pw=0 time=236644 us)
          9   TABLE ACCESS FULL DEPT (cr=3 pr=2 pw=0 time=6392 us)
     100000   TABLE ACCESS FULL EMP (cr=424 pr=400 pw=0 time=28159 us)
    
    
    
    -- 3. 테스트_3
    SELECT /*+ USE_HASH(D E) */
           *
    FROM   (select rownum, emp.* from EMP order by empno) e,
           DEPT D
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.004          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch    10002    0.190        0.752        400        407          0     100003
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total    10004    0.190        0.757        400        407          0     100003
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
     100003   HASH JOIN OUTER (cr=407 pr=400 pw=0 time=623806 us)
          9    TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=105 us)
     100000    VIEW  (cr=404 pr=400 pw=0 time=422547 us)
     100000     SORT ORDER BY (cr=404 pr=400 pw=0 time=322541 us)
     100000      COUNT  (cr=404 pr=400 pw=0 time=102073 us)
     100000       TABLE ACCESS FULL EMP (cr=404 pr=400 pw=0 time=2072 us)
    

IV. Full Outer 조인

1. 'Left Outer 조인 + Union All + Anti 조인(Not Exists 필터)' 이용


-- 1. 테이블 생성
DROP TABLE DEPOSIT PURGE;
DROP TABLE PAYMENT PURGE;

EXEC DBMS_RANDOM.SEED(150);

CREATE TABLE DEPOSIT
AS
SELECT ROWNUM CNT
     , ROUND(DBMS_RANDOM.VALUE(1, 20)) CUSTOMER_ID
     , ROUND(DBMS_RANDOM.VALUE(1000, 100000),-2) DEPOSIT_AMOUNT
FROM DUAL CONNECT BY LEVEL <= 10;

CREATE TABLE PAYMENT
AS
SELECT ROWNUM CNT
     , ROUND(DBMS_RANDOM.VALUE(1, 20)) CUSTOMER_ID
     , ROUND(DBMS_RANDOM.VALUE(1000, 10000),-2) PAYMENT_AMOUNT
FROM DUAL CONNECT BY LEVEL <= 10;

-- 2. 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPOSIT');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PAYMENT');

-- 3. Full Outer 데이터 출력
SELECT A.CUSTOMER_ID,
       A.DEPOSIT_AMOUNT,
       B.PAYMENT_AMOUNT
FROM   (SELECT CUSTOMER_ID, 
               SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
        FROM   DEPOSIT
        GROUP BY CUSTOMER_ID) A,
       (SELECT CUSTOMER_ID, 
               SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
        FROM   PAYMENT
        GROUP BY CUSTOMER_ID) B
WHERE  B.CUSTOMER_ID(+) = A.CUSTOMER_ID
UNION ALL
SELECT CUSTOMER_ID, 
       NULL,
       PAYMENT_AMOUNT
FROM   (SELECT CUSTOMER_ID,
               SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
        FROM   PAYMENT
        GROUP BY CUSTOMER_ID) A
WHERE  NOT EXISTS (SELECT 1
                   FROM   DEPOSIT
                   WHERE  CUSTOMER_ID = A.CUSTOMER_ID)
;

CUSTOMER_ID DEPOSIT_AMOUNT PAYMENT_AMOUNT
----------- -------------- --------------
          2          23900           6200
          3          26600           2300
         13           6800          14500
         19          40400           6900
          1          23100               
          6          71000               
          4         121900               
          8          95700               
         18          34300               
          7                          3900
         17                          9200
         16                          7500
          9                          1300

13 rows selected.

-- 4. 실행계획
EXPLAIN PLAN FOR
SELECT A.CUSTOMER_ID,
       A.DEPOSIT_AMOUNT,
       B.PAYMENT_AMOUNT
FROM   (SELECT CUSTOMER_ID, 
               SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
        FROM   DEPOSIT
        GROUP BY CUSTOMER_ID) A,
       (SELECT CUSTOMER_ID, 
               SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
        FROM   PAYMENT
        GROUP BY CUSTOMER_ID) B
WHERE  B.CUSTOMER_ID(+) = A.CUSTOMER_ID
UNION ALL
SELECT CUSTOMER_ID, 
       NULL,
       PAYMENT_AMOUNT
FROM   (SELECT CUSTOMER_ID,
               SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
        FROM   PAYMENT
        GROUP BY CUSTOMER_ID) A
WHERE  NOT EXISTS (SELECT 1
                   FROM   DEPOSIT
                   WHERE  CUSTOMER_ID = A.CUSTOMER_ID)
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------------------------
| 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| DEPOSIT |    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| PAYMENT |    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 | PAYMENT |    10 |    60 |     3   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL | DEPOSIT |    10 |    30 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
                                                                                 
Predicate Information (identified by operation id):                              
---------------------------------------------------                              
                                                                                 
   2 - access("B"."CUSTOMER_ID"(+)="A"."CUSTOMER_ID")                            
  10 - access("CUSTOMER_ID"="CUSTOMER_ID")    
  
-- 5. Trace 내용
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.002          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        3    0.010        0.010          0         12          0         13
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        5    0.010        0.012          0         12          0         13

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     13   UNION-ALL  (cr=12 pr=0 pw=0 time=9851 us)
      9    HASH JOIN OUTER (cr=6 pr=0 pw=0 time=9843 us)
      9     VIEW  (cr=3 pr=0 pw=0 time=3492 us)
      9      HASH GROUP BY (cr=3 pr=0 pw=0 time=3481 us)
     10       TABLE ACCESS FULL DEPOSIT (cr=3 pr=0 pw=0 time=66 us)
      8     VIEW  (cr=3 pr=0 pw=0 time=350 us)
      8      HASH GROUP BY (cr=3 pr=0 pw=0 time=342 us)
     10       TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=46 us)
      4    HASH GROUP BY (cr=6 pr=0 pw=0 time=292 us)
      5     HASH JOIN ANTI (cr=6 pr=0 pw=0 time=150 us)
     10      TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=20 us)
     10      TABLE ACCESS FULL DEPOSIT (cr=3 pr=0 pw=0 time=27 us)                                     

2. ANSI Full Outer 조인

  • ANSI 구문으로 인해 사용법은 더 편해졌지만 1번 내용과 동일한 실행계획 보임
    
    -- 1. ANSI 구문을 활용한 출력 결과 
    SELECT NVL(A.CUSTOMER_ID, B.CUSTOMER_ID) CUSTOMER_ID,
           A.DEPOSIT_AMOUNT,
           B.PAYMENT_AMOUNT
    FROM   (SELECT CUSTOMER_ID,
                   SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
            FROM   DEPOSIT
            GROUP BY CUSTOMER_ID) A 
           FULL OUTER JOIN
           (SELECT CUSTOMER_ID,
                   SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
            FROM   PAYMENT
            GROUP BY CUSTOMER_ID) B
           ON A.CUSTOMER_ID = B.CUSTOMER_ID
    ;
    
    CUSTOMER_ID DEPOSIT_AMOUNT PAYMENT_AMOUNT 
    ----------- -------------- -------------- 
              2          23900           6200 
              3          26600           2300 
             13           6800          14500 
             19          40400           6900 
              1          23100                
              6          71000                
              4         121900                
              8          95700                
             18          34300                
              7                          3900 
             17                          9200 
             16                          7500 
              9                          1300 
    
    13 rows selected.
    ;
    
    -- 2. 실행계획
    EXPLAIN PLAN FOR
    SELECT NVL(A.CUSTOMER_ID, B.CUSTOMER_ID) CUSTOMER_ID,
           A.DEPOSIT_AMOUNT,
           B.PAYMENT_AMOUNT
    FROM   (SELECT CUSTOMER_ID,
                   SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
            FROM   DEPOSIT
            GROUP BY CUSTOMER_ID) A 
           FULL OUTER JOIN
           (SELECT CUSTOMER_ID,
                   SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
            FROM   PAYMENT
            GROUP BY CUSTOMER_ID) B
           ON A.CUSTOMER_ID = B.CUSTOMER_ID
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    
    ------------------------------------------------------------------------------------
    | 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 |   468 |     9  (34)| 00:00:01 |
    |   4 |     VIEW                 |         |     9 |   234 |     4  (25)| 00:00:01 |
    |   5 |      HASH GROUP BY       |         |     9 |    63 |     4  (25)| 00:00:01 |
    |   6 |       TABLE ACCESS FULL  | DEPOSIT |    10 |    70 |     3   (0)| 00:00:01 |
    |   7 |     VIEW                 |         |     8 |   208 |     4  (25)| 00:00:01 |
    |   8 |      HASH GROUP BY       |         |     8 |    48 |     4  (25)| 00:00:01 |
    |   9 |       TABLE ACCESS FULL  | PAYMENT |    10 |    60 |     3   (0)| 00:00:01 |
    |  10 |    HASH GROUP BY         |         |     8 |    48 |    19   (6)| 00:00:01 |
    |* 11 |     FILTER               |         |       |       |            |          |
    |  12 |      TABLE ACCESS FULL   | PAYMENT |    10 |    60 |     3   (0)| 00:00:01 |
    |  13 |      SORT GROUP BY NOSORT|         |     1 |     3 |     3   (0)| 00:00:01 |
    |* 14 |       TABLE ACCESS FULL  | DEPOSIT |     1 |     3 |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------
                                                                                        
    Predicate Information (identified by operation id):                                 
    ---------------------------------------------------                                 
                                                                                        
       3 - access("A"."CUSTOMER_ID"="B"."CUSTOMER_ID"(+))                               
      11 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "DEPOSIT" "DEPOSIT"          
                  WHERE "CUSTOMER_ID"=:B1 GROUP BY "CUSTOMER_ID"))                      
      14 - filter("CUSTOMER_ID"=:B1)                                                    
    ;
    
    -- 3. Trace 내용(I/O도 더 증가함)
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.004          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        3    0.010        0.010          0         33          0         13
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        5    0.010        0.015          0         33          0         13
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
         13   VIEW  (cr=33 pr=0 pw=0 time=10088 us)
         13    UNION-ALL  (cr=33 pr=0 pw=0 time=10086 us)
          9     HASH JOIN OUTER (cr=6 pr=0 pw=0 time=10079 us)
          9      VIEW  (cr=3 pr=0 pw=0 time=9189 us)
          9       HASH GROUP BY (cr=3 pr=0 pw=0 time=9180 us)
         10        TABLE ACCESS FULL DEPOSIT (cr=3 pr=0 pw=0 time=112 us)
          8      VIEW  (cr=3 pr=0 pw=0 time=448 us)
          8       HASH GROUP BY (cr=3 pr=0 pw=0 time=447 us)
         10        TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=44 us)
          4     HASH GROUP BY (cr=27 pr=0 pw=0 time=359 us)
          5      FILTER  (cr=27 pr=0 pw=0 time=335 us)
         10       TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=44 us)
          4       SORT GROUP BY NOSORT (cr=24 pr=0 pw=0 time=153 us)
          4        TABLE ACCESS FULL DEPOSIT (cr=24 pr=0 pw=0 time=127 us)
    

3. Native Hash Full Outer 조인

  • 위처럼 ANSI Full Outer 조인을 사용할 때 I/O가 나빠지는 것을 극복하기 위해
    오라클은 11g에서 'Native Hash Full Outer 조인'을 선보였고 10.2.0.4에서도
    OPT_PARAM 힌트(_optimizer_native_full_outer_join)를 사용하여 가능하도록 지원한다.
    
    -- 1. ANSI 구문을 활용한 출력 결과 
    SELECT /*+ OPT_PARAM('_optimizer_native_full_outer_join', 'force') */
           NVL(A.CUSTOMER_ID, B.CUSTOMER_ID) CUSTOMER_ID,
           A.DEPOSIT_AMOUNT,
           B.PAYMENT_AMOUNT
    FROM   (SELECT CUSTOMER_ID,
                   SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT
            FROM   DEPOSIT
            GROUP BY CUSTOMER_ID) A 
           FULL OUTER JOIN
           (SELECT CUSTOMER_ID,
                   SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
            FROM   PAYMENT
            GROUP BY CUSTOMER_ID) B
           ON A.CUSTOMER_ID = B.CUSTOMER_ID
    ;
    
    CUSTOMER_ID DEPOSIT_AMOUNT PAYMENT_AMOUNT 
    ----------- -------------- -------------- 
              6          71000                
              7                          3900 
             17                          9200 
              8          95700                
             16                          7500 
              3          26600           2300 
              1          23100                
              2          23900           6200 
             18          34300                
             19          40400           6900 
             13           6800          14500 
              4         121900                
              9                          1300 
    
    13 rows selected.
    ;
    
    -- 2. 실행계획과 Trace는 버전이 낮은 관계로 테스트 못함 -_-;
    

4. Union All을 이용한 Full Outer 조인

  • 하지만 'Native Hash Full Outer 조인'과 동일한 I/O 효과를 볼 수 있는 방법이 있는데
    그것은 Outer 대상의 양쪽 키를 NULL을 포함하여 UNION ALL로 묶은 뒤 GROUP BY로 압축하게 되면
    동일한 결과를 보여주면서 성능향상을 높일 수 있다.
    
    -- 1. 추출 결과 
    SELECT CUSTOMER_ID,
           SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT,
           SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
    FROM   (SELECT CUSTOMER_ID, 
                   DEPOSIT_AMOUNT,
                   TO_NUMBER(NULL) PAYMENT_AMOUNT
            FROM   DEPOSIT
            UNION ALL
            SELECT CUSTOMER_ID, 
                   TO_NUMBER(NULL) DEPOSIT_AMOUNT,
                   PAYMENT_AMOUNT
            FROM   PAYMENT)
    GROUP BY CUSTOMER_ID
    ;
    
    CUSTOMER_ID DEPOSIT_AMOUNT PAYMENT_AMOUNT 
    ----------- -------------- -------------- 
              6          71000                
              7                          3900 
             17                          9200 
              8          95700                
             16                          7500 
              3          26600           2300 
              1          23100                
              2          23900           6200 
             18          34300                
             19          40400           6900 
             13           6800          14500 
              4         121900                
              9                          1300 
    
    13 rows selected.
    ;
    
    -- 2. 실행계획
    EXPLAIN PLAN FOR
    SELECT CUSTOMER_ID,
           SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT,
           SUM(PAYMENT_AMOUNT) PAYMENT_AMOUNT
    FROM   (SELECT CUSTOMER_ID, 
                   DEPOSIT_AMOUNT,
                   TO_NUMBER(NULL) PAYMENT_AMOUNT
            FROM   DEPOSIT
            UNION ALL
            SELECT CUSTOMER_ID, 
                   TO_NUMBER(NULL) DEPOSIT_AMOUNT,
                   PAYMENT_AMOUNT
            FROM   PAYMENT)
    GROUP BY CUSTOMER_ID
    ;
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    
    -- 3. Trace 내용(I/O는 가장 좋음)
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.001          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        3    0.000        0.003          0          6          0         13
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        5    0.000        0.005          0          6          0         13
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
         13   HASH GROUP BY (cr=6 pr=0 pw=0 time=3492 us)
         20    VIEW  (cr=6 pr=0 pw=0 time=98 us)
         20     UNION-ALL  (cr=6 pr=0 pw=0 time=96 us)
         10      TABLE ACCESS FULL DEPOSIT (cr=3 pr=0 pw=0 time=62 us)
         10      TABLE ACCESS FULL PAYMENT (cr=3 pr=0 pw=0 time=31 us)
    

문서에 대하여

문서정보

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