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

4. 조인 순서의 중요성




04 조인 순서의 중요성

  • 조인 순서에 따라 쿼리 수행 성능이 달라질 수 있다.
  • 이 장에서는 NL조인에 있어서 어떤 테이블을 드라이빙하는것이 유리한지를 예제를 통해 확인한다.
예시자료
  • 10만 고객 테이블 생성
    CREATE TABLE 고객
    AS
    SELECT LEVEL 고객번호
          , MOD(LEVEL, 3) + 1 납입방법코드
          , DECODE(MOD(LEVEL, 10)
         , 1, '서울', 2, '대전', 3, '대구', 4, '부산', 5, '찍고'
         , 6, '아하', 7, '경기', 8, '충청', 9, '전라', 0, '경상'
         ) 거주지역
      FROM dual
     CONNECT BY LEVEL <= 100000
    ;
    
    ALTER TABLE 고객 ADD CONSTRAINT pk_고객 PRIMARY KEY(고객번호);
    
    CREATE INDEX idx_1 ON 고객(거주지역);
    
    CREATE INDEX idx_2 ON 고객(납입방법코드);
    
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '고객', CASCADE => TRUE);
    
    EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '납입방법', CASCADE => TRUE);
    
  • 납입방법 코드테이블 생성
    CREATE TABLE 납입방법
    AS
    SELECT 1 납입방법코드, '신용카드' 납입방법 FROM dual
    UNION ALL SELECT 2, '자동이체' FROM dual
    UNION ALL SELECT 3, '지로'     FROM dual
    ;
    
    ALTER TABLE 납입방법
    ADD CONSTRAINT pk_납입방법 PRIMARY KEY(납입방법코드)
    ;
    
  • 조인
    SET AUTOT TRACE;
    SET LINESIZE 200;
    
    SELECT a.납입방법명, b.*
      FROM 납입방법 a, 고객 b
     WHERE b.납입방법코드 = a.납입방법코드
    ;
    
    100000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 734007503
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      | 99491 |  2720K|    89   (7)| 00:00:02 |
    |*  1 |  HASH JOIN         |      | 99491 |  2720K|    89   (7)| 00:00:02 |
    |   2 |   TABLE ACCESS FULL| 납입 |     3 |    42 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| 고객 | 99491 |  1360K|    83   (4)| 00:00:02 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           6931  consistent gets
              0  physical reads
              0  redo size
        3556195  bytes sent via SQL*Net to client
          73710  bytes received via SQL*Net from client
           6668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         100000  rows processed
    

필터조건이 없을 때

  • 고객테이블 드라이빙
    SQL> SELECT /*+ LEADING(b) USE_NL(a) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5  ;
    
    100000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1676947890
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         | 99491 |  2720K|   223  (64)| 00:00:03 |
    |   1 |  NESTED LOOPS                |         | 99491 |  2720K|   223  (64)| 00:00:03 |
    |   2 |   TABLE ACCESS FULL          | 고객    | 99491 |  1360K|    83   (4)| 00:00:02 |
    |   3 |   TABLE ACCESS BY INDEX ROWID| 납입방법|     1 |    14 |     1   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN         | PK_납입 |     1 |       |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
         113596  consistent gets
              1  physical reads
              0  redo size
        3556195  bytes sent via SQL*Net to client
          73710  bytes received via SQL*Net from client
           6668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         100000  rows processed
    
  • 납입방법 테이블 드라이빙
    SQL> SELECT /*+ LEADING(a) USE_NL(b) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5  ;
    
    100000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 920073752
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      | 99491 |  2720K|   250   (4)| 00:00:04 |
    |   1 |  NESTED LOOPS      |      | 99491 |  2720K|   250   (4)| 00:00:04 |
    |   2 |   TABLE ACCESS FULL| 납입 |     3 |    42 |     3   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| 고객 | 33164 |   453K|    82   (4)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
            213  recursive calls
              0  db block gets
           7537  consistent gets
              0  physical reads
              0  redo size
        2536173  bytes sent via SQL*Net to client
          73710  bytes received via SQL*Net from client
           6668  SQL*Net roundtrips to/from client
              5  sorts (memory)
              0  sorts (disk)
         100000  rows processed
    
  • 납입방법 테이블 드라이빙 - Index 힌트
    SQL> SELECT /*+ LEADING(a) USE_NL(b) INDEX(b idx_2) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5  ;
    
    100000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1301791307
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       | 99491 |  2720K|  1034   (1)| 00:00:13 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| 고객  | 33164 |   453K|   344   (1)| 00:00:05 |
    |   2 |   NESTED LOOPS              |       | 99491 |  2720K|  1034   (1)| 00:00:13 |
    |   3 |    TABLE ACCESS FULL        | 납입방|     3 |    42 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX RANGE SCAN         | IDX_2 | 33333 |       |    66   (2)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          14313  consistent gets
            196  physical reads
              0  redo size
        2536173  bytes sent via SQL*Net to client
          73710  bytes received via SQL*Net from client
           6668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         100000  rows processed
    
정리

1. 고객 드라이빙 (고객테이블 =10만=> 납입방법코드IDX =10만=> 납입방법테이블, 총 20만번 엑세스)
2. 납입 드라이빙 (납입방법 =3번=> 납입방법코드IDX =10만=> 고객, 총 10만3번 엑세스)

  • 필터조건이 없을 땐 작은쪽 집합을 드라이빙 하는것이 유리하다.

필터조건이 있을 때

  • 필터조건 : 거주지역 = '부산' (전체 자료중 10% 차지)
  • 고객테이블 드라이빙
    SQL> SELECT /*+ LEADING(b) USE_NL(a) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5     AND b.거주지역 = '부산'
      6  ;
    
    10000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1676947890
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |  9949 |   272K|    99  (19)| 00:00:02 |
    |   1 |  NESTED LOOPS                |         |  9949 |   272K|    99  (19)| 00:00:02 |
    |*  2 |   TABLE ACCESS FULL          | 고객    |  9949 |   136K|    84   (5)| 00:00:02 |
    |   3 |   TABLE ACCESS BY INDEX ROWID| 납입방법|     1 |    14 |     1   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN         | PK_납입 |     1 |       |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("B"."거주지역"='부산')
       4 - access("B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          11613  consistent gets
              0  physical reads
              0  redo size
         324238  bytes sent via SQL*Net to client
           7710  bytes received via SQL*Net from client
            668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          10000  rows processed
    
  • 고객테이블 드라이빙 - Index 힌트
    SQL> SELECT /*+ LEADING(b) USE_NL(a) INDEX(b idx_1) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5     AND b.거주지역 = '부산'
      6  ;
    
    10000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1945849362
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |  9949 |   272K|   318   (5)| 00:00:04 |
    |   1 |  NESTED LOOPS                |         |  9949 |   272K|   318   (5)| 00:00:04 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| 고객    |  9949 |   136K|   303   (1)| 00:00:04 |
    |*  3 |    INDEX RANGE SCAN          | IDX_1   | 10000 |       |    26   (0)| 00:00:01 |
    |   4 |   TABLE ACCESS BY INDEX ROWID| 납입방법|     1 |    14 |     1   (0)| 00:00:01 |
    |*  5 |    INDEX UNIQUE SCAN         | PK_납입 |     1 |       |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("B"."거주지역"='부산')
       5 - access("B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          12288  consistent gets
             32  physical reads
              0  redo size
         324238  bytes sent via SQL*Net to client
           7710  bytes received via SQL*Net from client
            668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          10000  rows processed
    
  • 납입방법 테이블 드라이빙
    SQL> SELECT /*+ LEADING(a) USE_NL(b) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5     AND b.거주지역 = '부산'
      6  ;
    
    10000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 920073752
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |  9949 |   272K|   253   (6)| 00:00:04 |
    |   1 |  NESTED LOOPS      |      |  9949 |   272K|   253   (6)| 00:00:04 |
    |   2 |   TABLE ACCESS FULL| 납입 |     3 |    42 |     3   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| 고객 |  3316 | 46424 |    83   (5)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("B"."거주지역"='부산' AND "B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           1510  consistent gets
              0  physical reads
              0  redo size
         184278  bytes sent via SQL*Net to client
           7710  bytes received via SQL*Net from client
            668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          10000  rows processed
    
  • 납입방법 테이블 드라이빙 - Index 힌트
    SQL> SELECT /*+ LEADING(a) USE_NL(b) INDEX(b idx_2) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5     AND b.거주지역 = '부산'
      6  ;
    
    10000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1301791307
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |  9949 |   272K|  1035   (1)| 00:00:13 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| 고객  |  3316 | 46424 |   344   (1)| 00:00:05 |
    |   2 |   NESTED LOOPS              |       |  9949 |   272K|  1035   (1)| 00:00:13 |
    |   3 |    TABLE ACCESS FULL        | 납입방|     3 |    42 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX RANGE SCAN         | IDX_2 | 33333 |       |    66   (2)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("B"."거주지역"='부산')
       4 - access("B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           2361  consistent gets
              0  physical reads
              0  redo size
         184278  bytes sent via SQL*Net to client
           7710  bytes received via SQL*Net from client
            668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          10000  rows processed
    
  • 납입방법 테이블 드라이빙 - Index 추가(납입방법 + 거주지역)
    SQL> CREATE INDEX idx_3 ON 고객(납입방법코드, 거주지역);
    
    인덱스가 생성되었습니다.
    
    SQL> SELECT /*+ LEADING(a) USE_NL(b) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5     AND b.거주지역 = '부산'
      6  ;
    
    10000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 920073752
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |  9949 |   272K|   253   (6)| 00:00:04 |
    |   1 |  NESTED LOOPS      |      |  9949 |   272K|   253   (6)| 00:00:04 |
    |   2 |   TABLE ACCESS FULL| 납입 |     3 |    42 |     3   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| 고객 |  3316 | 46424 |    83   (5)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("B"."거주지역"='부산' AND "B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           1510  consistent gets
              0  physical reads
              0  redo size
         184278  bytes sent via SQL*Net to client
           7710  bytes received via SQL*Net from client
            668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          10000  rows processed
    
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '고객', CASCADE => TRUE);
    
    PL/SQL 처리가 정상적으로 완료되었습니다.
    
    SQL> SELECT /*+ LEADING(a) USE_NL(b) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5     AND b.거주지역 = '부산'
      6  ;
    
    10000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 920073752
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      | 10491 |   286K|   253   (6)| 00:00:04 |
    |   1 |  NESTED LOOPS      |      | 10491 |   286K|   253   (6)| 00:00:04 |
    |   2 |   TABLE ACCESS FULL| 납입 |     3 |    42 |     3   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| 고객 |  3497 | 48958 |    83   (5)| 00:00:02 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("B"."거주지역"='부산' AND "B"."납입방법코드"="A"."납입방법코드")
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           1510  consistent gets
              0  physical reads
              0  redo size
         184278  bytes sent via SQL*Net to client
           7710  bytes received via SQL*Net from client
            668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          10000  rows processed
    
    SQL> SELECT /*+ LEADING(a) USE_NL(b) INDEX(b idx_3) */
      2         a.납입방법명, b.*
      3    FROM 납입방법 a, 고객 b
      4   WHERE b.납입방법코드 = a.납입방법코드
      5     AND b.거주지역 = '부산'
      6  ;
    
    10000 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2190748210
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       | 10491 |   286K|   902   (1)| 00:00:11 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| 고객  |  3497 | 48958 |   300   (1)| 00:00:04 |
    |   2 |   NESTED LOOPS              |       | 10491 |   286K|   902   (1)| 00:00:11 |
    |   3 |    TABLE ACCESS FULL        | 납입방|     3 |    42 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX RANGE SCAN         | IDX_3 |  3497 |       |     9   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("B"."납입방법코드"="A"."납입방법코드" AND "B"."거주지역"='부산')
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           2145  consistent gets
              0  physical reads
              0  redo size
         184278  bytes sent via SQL*Net to client
           7710  bytes received via SQL*Net from client
            668  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          10000  rows processed
    
정리

1. 고객 드라이빙 (거주지역IDX =1만=> 고객테이블 =1만=> 납입방법코드IDX =1만=> 납입방법테이블, 총 3만번 엑세스)
2. 납입 드라이빙 (납입방법 =3번=> 납입방법코드IDX =10만=> 고객(10만번 엑세스중 10%만 조회 90%의 비효율 존재), 총 10만3번 엑세스)
3. 납입+거주지역 인덱스 추가 (납입방법 =3번=> 납입방법+거주지역IDX =1만=> 고객(비효율 없음), 총 1만3번 엑세스)

  • 필터조건이 있을 땐 인덱스 구성에 따라 유.불리가 결정된다.
  • 비효율이 없게끔 인덱스를 잘 구성한다면 작은쪽 집합을 드라이빙 하는것이 유리하다.

소트머지조인

  • 소트머지 조인의 성능은 NL조인과 달리 렌덤엑세스 부하보다는 소트부하에 의해 발생한다.
  • 디스크 소트가 발생할정도로 큰 테이블이 포함된 경우 큰 테이블 드라이빙이 유리
  • 메모리 소트만으로 해결괴는 경우 작은 테이블 드라이빙이 유리

해시조인

  • Hash Area에 Build Input을 모두 채울 수 있느냐가 관건
  • 작은 테이블 드라이빙이 유리
3개 이상 테이블 조인시 유의사항
  • 조인컬럼에 대한 상수조건은 조인문을 타고 다른 테이블로 전이된다.(4장5절 '조건절이행' 참조)
  • 상수조건이 아닌 조인 조건 자체는 이러한 전이가 일어나지 않는다.
  • 따라서 사용자가 최적의 조인순서를 결정하고 그 순서에 따라 조인조건을 기술해 주는것이 중요하다.

문서정보

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