Notice
Recent Posts
Recent Comments
Link
«   2024/11   »
일 월 화 수 목 금 토
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
Tags more
Archives
Today
Total
관리 메뉴

엉망진창

조인 순서의 중요성 본문

Study_DB/DB_Common

조인 순서의 중요성

엉망진창 2011. 12. 14. 10:28
  • 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=6258991&

    • 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절 '조건절이행' 참조)
    • 상수조건이 아닌 조인 조건 자체는 이러한 전이가 일어나지 않는다.
    • 따라서 사용자가 최적의 조인순서를 결정하고 그 순서에 따라 조인조건을 기술해 주는것이 중요하다.
  • 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=6258991&