메뉴 건너뛰기

bysql.net

4. 조인 순서의 중요성

2011.03.28 03:17

운영자 조회 수:14236

04.조인 순서의 중요성
   ·테이블을 연결하는 유일한 수단 : 조인
   ·조인 순서에 따라 쿼리 수행성능이 달라짐
   ·가정 : 고객테이블 (10만 고객)
            납입방법 테이블(신용카드, 자동이체, 지로)
            NL방식으로 조인할 때의 영향분석(연결 컬럼에 인덱스존재)
           
   ▶테스트 환경 데이터 구축
   DROP   TABLE "고객";
   CREATE TABLE "고객"
   AS
   SELECT LEVEL  AS CUST_ID
        , CEIL(DBMS_RANDOM.VALUE(0, 10)) AS LOC_CD
        , CEIL(DBMS_RANDOM.VALUE(0,  3)) AS PAY_CD
     FROM DUAL
   CONNECT BY LEVEL < 100000;
  
   CREATE INDEX PAY_CD_IX ON "고객"(PAY_CD);
  
  
   CREATE TABLE "납입방법"
   AS
   SELECT 1 AS PAY_CD, '신용카드' AS PAY_NM FROM DUAL UNION ALL
   SELECT 2 AS PAY_CD, '자동이체' AS PAY_NM FROM DUAL UNION ALL
   SELECT 3 AS PAY_CD, '지로'     AS PAY_NM FROM DUAL;
  
   DROP          INDEX PK_PAY_CD;
   CREATE UNIQUE INDEX PK_PAY_CD ON "납입방법" (PAY_CD);
  
   ▶필터 조건이 없을 때
   ·고객 테이브을 먼저 드라이빙 하는 경우
   SELECT /*+ LEADING(B) USE_NL(A) */ A.PAY_NM, B.*
     FROM 납입방법 A, 고객 B
    WHERE B.PAY_CD = A.PAY_CD;
  
   ------------------------------------------------------------------------------------------
   | Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
   ------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |           |   115K|  6544K|   115K  (1)| 00:23:08 |
   |   1 |  NESTED LOOPS                |           |       |       |            |          |
   |   2 |   NESTED LOOPS               |           |   115K|  6544K|   115K  (1)| 00:23:08 |
   |   3 |    TABLE ACCESS FULL         | 고객      |   115K|  4400K|    65   (2)| 00:00:01 |
   |*  4 |    INDEX RANGE SCAN          | PK_PAY_CD |     1 |       |     0   (0)| 00:00:01 |
   |   5 |   TABLE ACCESS BY INDEX ROWID| 납입방법  |     1 |    19 |     1   (0)| 00:00:01 |
   ------------------------------------------------------------------------------------------
   (그림2-12)를 보면 고객 테이블에서 납입방법코드 인덱스로 조인 액세스할 때 10만번의 Random
   액세스가 발생하고, 납입방법 테이블로 액세스할 때 다시 10만 번의 Random 액세스가 발생함으로
   총 20만번의 Random 액세스가 발생함
  
   ·납입방법 테이블을 먼저 드라이빙 하는 경우 (책 내용과는 달리 강제로 Index 조인을 유도함)
   SELECT /*+ LEADING(A) USE_NL(B) INDEX(B, PAY_CD_IX) */ A.PAY_NM, B.*
     FROM 납입방법 A, 고객 B
    WHERE B.PAY_CD = A.PAY_CD;
   ------------------------------------------------------------------------------------------
   | Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
   ------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |           |   115K|  6544K|   860   (1)| 00:00:11 |
   |   1 |  NESTED LOOPS                |           |       |       |            |          |
   |   2 |   NESTED LOOPS               |           |   115K|  6544K|   860   (1)| 00:00:11 |
   |   3 |    TABLE ACCESS FULL         | 납입방법  |     3 |    57 |     3   (0)| 00:00:01 |
   |*  4 |    INDEX RANGE SCAN          | PAY_CD_IX | 38513 |       |    65   (0)| 00:00:01 |
   |   5 |   TABLE ACCESS BY INDEX ROWID| 고객      | 38513 |  1466K|   286   (1)| 00:00:04 |
   ------------------------------------------------------------------------------------------
   (그림2-13)를 보면 납입 테이블에서 고객쪽 납입방법 인덱스로 조인을 시도할 때 3번의 Random
   액세스가 발생한다. 고객 테이블을 액세스힐 때는 10만 번의 Random 액세스가 발생해, 총 100,003번의
   Random 액세스가 발생한다.
  
   ★결론★
   다른 필터 조건이 없는 상황에서는 작은 쪽 집합을 드라이빙 하는 것이 유리함을 알 수 있다.
  
   ▶필터 조건이 있을 때
   ·고객 테이블의 거주지역 컬럼에 인덱스가 있다고 가정(10% 라고 가정, )
     CREATE INDEX LOC_CD_IX ON 고객(LOC_CD);
  
   ·고객 테이브을 먼저 드라이빙 하는 경우
   SELECT /*+ LEADING(B) USE_NL(A) INDEX(B, LOC_CD_IX) */ A.PAY_NM, B.*
     FROM 납입방법 A, 고객 B
    WHERE B.PAY_CD = A.PAY_CD
      AND B.LOC_CD = 1;

   ------------------------------------------------------------------------------------------
   | Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
   ------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |           | 10499 |   594K| 10569   (1)| 00:02:07 |
   |   1 |  NESTED LOOPS                |           |       |       |            |          |
   |   2 |   NESTED LOOPS               |           | 10499 |   594K| 10569   (1)| 00:02:07 |
   |*  3 |    TABLE ACCESS FULL         | 고객      | 10499 |   399K|    65   (2)| 00:00:01 |
   |*  4 |    INDEX UNIQUE SCAN         | PK_PAY_CD |     1 |       |     0   (0)| 00:00:01 |
   |   5 |   TABLE ACCESS BY INDEX ROWID| 납입방법  |     1 |    19 |     1   (0)| 00:00:01 |
   ------------------------------------------------------------------------------------------
   (그림2-14)를 보면, 3차례에 걸친 Access 발생
   [거주지역] 인덱스를 통해 고객 테이블 액세스할 때 1만번의 Random Access 발생
   [납입방법코드] 인덱스로 조인 액세스할 때 1만번의 Random Access 발생
   [납입방법] 테이블을 액세스할 때도 1만번의 Random Access 발생
   ∴ 총 3만번의 Random 액세스가 발생
  
   ·납입방법 테이브을 먼저 드라이빙 하는 경우
   SELECT /*+ LEADING(A) USE_NL(B) INDEX(B, PAY_CD_IX) */ A.PAY_NM, B.*
     FROM 납입방법 A, 고객 B
    WHERE B.PAY_CD = A.PAY_CD
      AND B.LOC_CD = 1;

   ------------------------------------------------------------------------------------------
   | Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
   ------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |           |  9888 |   212K|   860   (1)| 00:00:11 |
   |   1 |  NESTED LOOPS                |           |       |       |            |          |
   |   2 |   NESTED LOOPS               |           |  9888 |   212K|   860   (1)| 00:00:11 |
   |   3 |    TABLE ACCESS FULL         | 납입방법  |     3 |    33 |     3   (0)| 00:00:01 |
   |*  4 |    INDEX RANGE SCAN          | PAY_CD_IX | 33333 |       |    65   (0)| 00:00:01 |
   |*  5 |   TABLE ACCESS BY INDEX ROWID| 고객      |  3296 | 36256 |   286   (1)| 00:00:04 |
   ------------------------------------------------------------------------------------------
  
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      4 - access("B"."PAY_CD"="A"."PAY_CD")
      5 - filter("B"."LOC_CD"=1)
   (그림2-15)를 보면 [그림2-13]과 똑같은 일량인 것을 알 수 있음, 2차례에 걸친 Access 발생
   [납입방법] 테이블에서 [고객].[납입방법코드] 인덱스로 Access할 때 3번의 Random Access 발생
   [고객].[납입방법코드] 인덱스에서 [고객]테이블로 Access할 때 10만번의 Random Access 발생
   (Id 기준으로 4번) 많은 Access가 있었지만 거주지역(LOC_CD = 1)에 의해 90% 가량이 버려지므로
   비효율이 존재함
  
   ·인덱스에 '거주지역' 컬럼을 추가함
     CREATE INDEX PAY_LOC_CD_IX ON 고객(PAY_CD, LOC_CD);
 
   ----------------------------------------------------------------------------------------------
   | Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
   ----------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |               |  9888 |   212K|   678   (0)| 00:00:09 |
   |   1 |  NESTED LOOPS                |               |       |       |            |          |
   |   2 |   NESTED LOOPS               |               |  9888 |   212K|   678   (0)| 00:00:09 |
   |   3 |    TABLE ACCESS FULL         | 납입방법      |     3 |    33 |     3   (0)| 00:00:01 |
   |*  4 |    INDEX RANGE SCAN          | PAY_LOC_CD_IX |  3296 |       |     7   (0)| 00:00:01 |
   |   5 |   TABLE ACCESS BY INDEX ROWID| 고객          |  3296 | 36256 |   225   (0)| 00:00:03 |
   ----------------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      4 - access("B"."PAY_CD"="A"."PAY_CD" AND "B"."LOC_CD"=1) 
     
   바로 직전의 PLAN과 비교해 보면 Id 4번 기준으로 일량이 약 90% 줄어듬. Predicate Information을
   살펴보면 Filter가 없어짐
  
   ★결론★
   다른 필터 조건이 있는 상황에서는 인덱스 구성에 따라 유·불리가 결정됨. 비효율이 없게끔
   인덱스를 잘 구성해 주기만 한다면 역시 작은 쪽 집합을 드라이빙하는 것이 유리함
  
   ▶소트 머지 조인과 해시 조인의 경우
     조인의 순서가 중요함
     ·소트머지 조인
       - Random Access 발생량보다는 Sort 부하에 의해 성능이 결정됨
         Disk Sort가 발생할 정도의 큰 테이블을 포함할 때는 큰 테이블을 드라이빙 하는 것이 빠름
         Memory Sort 발식으로 조인할 때는 작은 쪽 테이블을 드라이빙 하는 것이 조금 더 빠름
     ·해시     조인
       - Hash Area에 Build Input(선행집합)을 모두 채울 수가 있느냐가 관건이므로 작은 쪽 테이블 드라이빙이 유리
         (SELECT * FROM V$PARAMETER WHERE NAME LIKE '%hash%' 또는 SHOW PARAMETER hash(sql*plus))              
  
   ▶세 개 이상 테이블에 대한 조인문 기술할 때 주의사항
     p522 4장 5절 '조건절 이행' - 어려운 내용은 없음
     주요내용은 TP(Transitive Predicate - 조인절을 이용하여 다른 테이블에 상수조건을 생성시켜라)임
    
     SELECT E.*
       FROM EMPLOYEES   E
          , DEPARTMENTS D
      WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
        AND E.DEPARTMENT_ID = 50;    
    
     -------------------------------------------------------------------------------
     | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
     -------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |           |    45 |  3105 |     3   (0)| 00:00:01 |
     |*  1 |  TABLE ACCESS FULL| EMPLOYEES |    45 |  3105 |     3   (0)| 00:00:01 |
     -------------------------------------------------------------------------------
    
     EMPLOYEES와 DEPARTMENTS의 FK / PK 관계로 인해 DEPARTMENTS 테이블을 ACCESS 안함
     ALTER TABLE EMPLOYEES DISABLE CONSTRAINT EMP_DEPT_FK;
    
     --------------------------------------------------------------------------------------------------
     | Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
     --------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT             |                   |    45 |  3285 |     3   (0)| 00:00:01 |
     |   1 |  NESTED LOOPS                |                   |    45 |  3285 |     3   (0)| 00:00:01 |
     |*  2 |   INDEX UNIQUE SCAN          | DEPT_ID_PK        |     1 |     4 |     0   (0)| 00:00:01 |
     |   3 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    45 |  3105 |     3   (0)| 00:00:01 |
     |*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    45 |       |     0   (0)| 00:00:01 |
     --------------------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        2 - access("D"."DEPARTMENT_ID"=50)
        4 - access("E"."DEPARTMENT_ID"=50)
       
     다시 말해, 조건절에는 [E.DEPARTMENT_ID = 50]을 조건절로 사용되어 실제로는 드라이빙이 [EMPLOYEES]이
     되어야 하지만 옵티마이져의 쿼리 변환으로 인해 Id 2번 기준으로 [D.DEPARTMENT_ID = 50]으로 조건이
     전이되어 [DEPARTMENTS] 테이블을 먼저 드라이빙으로 처리함 또한 조인조건으로 인해 DEPARTMENT_ID의
     조건은 같아야 함으로 Id 4번 기준으로 생성됨. 옵티마이져가 SQL을 다음처럼 재작성 함
    
     SELECT E.*
       FROM EMPLOYEES   E
          , DEPARTMENTS D
      WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
        AND E.DEPARTMENT_ID = 50
        AND D.DEPARTMENT_ID = 50;        
 
     --------------------------------------------------------------------------------------------------
     | Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
     --------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT             |                   |     4 |   292 |     1   (0)| 00:00:01 |
     |   1 |  NESTED LOOPS                |                   |     4 |   292 |     1   (0)| 00:00:01 |
     |*  2 |   INDEX UNIQUE SCAN          | DEPT_ID_PK        |     1 |     4 |     0   (0)| 00:00:01 |
     |   3 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     4 |   276 |     1   (0)| 00:00:01 |
     |*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     4 |       |     0   (0)| 00:00:01 |
     --------------------------------------------------------------------------------------------------
     
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        2 - access("D"."DEPARTMENT_ID"=50)
        4 - access("E"."DEPARTMENT_ID"=50)
            filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")   
           
     자세한 내용은 4장에서 다시...
    
   ▶기타 RIGHT OUTER HASH JOIN
     SJR*, AJR* (Hash Join Right Semi / Anti)
     - Hash Semi / Anti Join 시 사용되는 서브쿼리 집합을 Build Input 집합으로 변환하라
     - 10g 부터 나오게 된 New Feature(★★★★★)

     Semi / Anti Join은 항상 메인 쿼리가 수행된 후 서브쿼리의 Data를 체크하는 방식이다.
     따라서 항상 후행집합이 될 수 밖에 없다. Hash Outer Join의 경우도 마찬가지로 (+)표시가 붙은 쪽의
     집합은 항상 후행집합이 될 수 밖에 없었다.
     10g 부터는 Hash Join Right(Semi / Anti / Outer) 기능이 아오게 되면서 서브쿼리 혹은 아우터 Join이
     되는 쪽의 집합이 선행집합이 될 수 있다. 대용량 DB에서 매우 유용하게 사용할 수 있다.
    
     1.테이블 생성 및 통계정보 생성
       CREATE TABLE BIG_EMP
       AS
       SELECT ROWNUM AS EMPLOYEE_ID
            , FIRST_NAME
            , LAST_NAME
            , EMAIL
            , PHONE_NUMBER
            , HIRE_DATE
            , JOB_ID
            , SALARY
            , COMMISSION_PCT
            , MANAGER_ID
            , DEPARTMENT_ID
         FROM EMPLOYEES A
            , (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;
      
       ALTER  TABLE BIG_EMP ADD (CONSTRAINT PK_BIG_EMP PRIMARY KEY(EMPLOYEE_ID) USING INDEX);
      
       EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'BIG_EMP', CASCADE => TRUE);
    
     2.Hash Semi Join 유도
       SELECT /*+ GATHER_PLAN_STATITSTICS */
              A.EMPLOYEE_ID
            , A.SALARY
         FROM BIG_EMP  A
        WHERE EXISTS (
                     SELECT /*+ USE_HASH(B) */
                            1
                       FROM DEPARTMENTS B
                      WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
                     );
      
       -----------------------------------------------------------------------------------
       | Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
       -----------------------------------------------------------------------------------
       |   0 | SELECT STATEMENT     |            |   212K|  3312K|   618   (2)| 00:00:08 |
       |*  1 |  HASH JOIN RIGHT SEMI|            |   212K|  3312K|   618   (2)| 00:00:08 | (★★★)
       |   2 |   INDEX FULL SCAN    | DEPT_ID_PK |    27 |   108 |     1   (0)| 00:00:01 |
       |   3 |   TABLE ACCESS FULL  | BIG_EMP    |   214K|  2507K|   615   (2)| 00:00:08 |
       -----------------------------------------------------------------------------------
       ▶작은 용량의 DEPT를 드라이빙 집합(Built Input)으로 선택하고 BIG_EMP 테이블을 후행(Probe) 테이블로
         Hash 조인함으로써 최적의 조인이 됨
      
     3.Hash Join Right Semi를 사용하지 않을 경우에는 서브쿼리 Unnesting 기능을 이용하면 작은 용량의
       테이블인 Dept를 드라이빙으로 선택할 수 있다.
    
       SELECT /*+ GATHER_PLAN_STATITSTICS ORDERED */
              A.EMPLOYEE_ID
            , A.SALARY
         FROM BIG_EMP  A
        WHERE EXISTS (
                     SELECT /*+ USE_HASH(B) */
                            1
                       FROM DEPARTMENTS B
                      WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
                     );
      
       ---------------------------------------------------------------------------------
       | Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
       ---------------------------------------------------------------------------------
       |   0 | SELECT STATEMENT   |            |   212K|  3312K|   619   (2)| 00:00:08 |
       |*  1 |  HASH JOIN         |            |   212K|  3312K|   619   (2)| 00:00:08 |
       |   2 |   SORT UNIQUE      |            |    27 |   108 |     1   (0)| 00:00:01 |  (★★★)
       |   3 |    INDEX FULL SCAN | DEPT_ID_PK |    27 |   108 |     1   (0)| 00:00:01 |
       |   4 |   TABLE ACCESS FULL| BIG_EMP    |   214K|  2507K|   615   (2)| 00:00:08 |
       ---------------------------------------------------------------------------------
       ▶강제로 MAIN 쿼리에 ORDERED 힌트를 주어 Semi Join이 아닌 서브쿼리 Unnesting 처리
         (ORDERED 힌트를 사용한 이유는 서브쿼리가 Semi Join에 실패할 경우 서브쿼리 Unnesting을 시도하게됨
          이때, 서브쿼리 블럭이 From 절의 가장 좌측으로 오기 때문임 - ★★★)
       ▶불필요한 Sort Unique 혹은 Hash Unique 작업이 추가적으로 발생함
         대용량의 조인시는 엄청난 부담이 될 수 밖에 없음에 Hash Join Right Semi가 나타난 이유임
    
     4.강제로 Hash Join Right Semi를 발생시키는 방법
       - QB_NAME을 이용한 Global Hint와 USE_HASH, SWAP_JOIN_INPUTS Hint
    
       SELECT /*+ GATHER_PLAN_STATITSTICS LEADING(A) USE_HASH(@SUB B) SWAP_JOIN_INPUTS(@SUB B) */
              A.EMPLOYEE_ID
            , A.SALARY
         FROM BIG_EMP  A
        WHERE EXISTS (
                     SELECT /*+ QB_NAME(SUB) */
                            1
                       FROM DEPARTMENTS B
                      WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID
                     );
      
       -----------------------------------------------------------------------------------
       | Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
       -----------------------------------------------------------------------------------
       |   0 | SELECT STATEMENT     |            |   212K|  3312K|   618   (2)| 00:00:08 |
       |*  1 |  HASH JOIN RIGHT SEMI|            |   212K|  3312K|   618   (2)| 00:00:08 |  (★★★)
       |   2 |   INDEX FULL SCAN    | DEPT_ID_PK |    27 |   108 |     1   (0)| 00:00:01 |
       |   3 |   TABLE ACCESS FULL  | BIG_EMP    |   214K|  2507K|   615   (2)| 00:00:08 |
       -----------------------------------------------------------------------------------
       ▶SWAP_JOIN_INPUTS : PROBE 집합(후행집합)을 강제로 BUILD INPUT 집합(선행집합)으로 바꾸는 HINT
    
     5.Hash Join Right Anti Plan으로 유도할려면 EXISTS 대신 NOT EXISTS로 바꾸면 된다
    
     6.Hash Outer Right Join
    
       SELECT /*+ LEADING(A) USE_HASH(B) SWAP_JOIN_INPUTS(B) */
              A.EMPLOYEE_ID
            , A.SALARY
            , B.DEPARTMENT_NAME
         FROM BIG_EMP     A
            , DEPARTMENTS B
        WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);
      
       -------------------------------------------------------------------------------------
       | Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
       -------------------------------------------------------------------------------------
       |   0 | SELECT STATEMENT      |             |   214K|  5851K|   635   (2)| 00:00:08 |
       |*  1 |  HASH JOIN RIGHT OUTER|             |   214K|  5851K|   635   (2)| 00:00:08 |  (★★★) 
       |   2 |   TABLE ACCESS FULL   | DEPARTMENTS |    27 |   432 |    18   (0)| 00:00:01 |
       |   3 |   TABLE ACCESS FULL   | BIG_EMP     |   214K|  2507K|   615   (2)| 00:00:08 |
       -------------------------------------------------------------------------------------
       ▶10g부터 나온 Hash Join Right(Semi / Anti / Outer) 기능을 적재적소에 활용한다면 대용량 집합간의
         Join 성능을 획기적으로 향상시킬 수 있다