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절 '조건절이행' 참조)
상수조건이 아닌 조인 조건 자체는 이러한 전이가 일어나지 않는다.
따라서 사용자가 최적의 조인순서를 결정하고 그 순서에 따라 조인조건을 기술해 주는것이 중요하다.

4. 조인 순서의 중요성 (by manon94) [2010.08.27]