포스트

HASH JOIN 에 SWAP_JOIN_INPUTS 사용

HASH JOIN 의 빌드 입력(Driving) 결과 집합이 후행 테이블보다 크면 메모리·CPU 가 낭비됩니다. SWAP_JOIN_INPUTS 힌트로 빌드/프로브 순서를 뒤집어 1 건짜리 작은 집합을 해시 테이블로 만들면 비용이 크게 줄어듭니다.

게시
By 반지현
읽는 시간 5분
HASH JOIN 에 SWAP_JOIN_INPUTS 사용

Driving (빌드 입력) 의 결과 집합이 후행 테이블보다 클 때 SWAP_JOIN_INPUTS 로 순서를 뒤집어 작은 집합을 해시 테이블로 만들 수 있습니다.

핵심 정리

  1. Hash Join 이나 NL Join 모두 Driving 테이블의 결과 집합에 따라 후행 테이블을 탐색하는 코스트가 결정됩니다.
  2. 따라서 Driving Table 의 결과 집합을 작게 하면 Hash Join 이나 NL Join 에 대한 Cost 를 낮출 수 있습니다.
  3. 이를 위해 Hash Join 에서는 SWAP_JOIN_INPUTS 를 사용하여 후행 테이블의 결과 집합이 작을 경우 순서를 바꿔서 Join 할 수 있습니다.

원본 쿼리

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT /*+ LEADING(C O S) USE_NL(O) USE_HASH(S)
           INDEX(C IX_T_CUST_CD) INDEX(O IX_T_ORDER1_ID_DY) FULL(S) */
       *
  FROM T_CUST         C,
       T_ORDER1       O,
       T_ORDER_STA1   S
 WHERE C.CUST_CD = 'z005'
   AND C.CUST_ID = O.CUST_ID
   AND O.ORDER_DY BETWEEN '20230901' AND '20231030'
   AND O.ORDER_STA_CD = S.ORDER_STA_CD
   AND S.ORDER_STA = '구매확정'
   AND S.CM IS NOT NULL
 ORDER BY O.ORDER_DY, C.CUST_ID;

실행 계획

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

----------------------------------------------------------------------------------------------
| ID | Operation               | Name              | Rows | Elaps. Time   | CR Gets | Starts |
----------------------------------------------------------------------------------------------
|  1 | ORDER BY (SORT)         |                   |  700 | 00:00:00.0003 |       0 |      1 |
|  2 |  HASH JOIN              |                   |  700 | 00:00:00.0002 |       0 |      1 |
|  3 |   INDEX JOIN            |                   |  700 | 00:00:00.0007 |       0 |      1 |  ← 해시 테이블 (Driving) — INDEX JOIN 서브트리
|  4 |    TABLE ACCESS (ROWID) | T_CUST            |  350 | 00:00:00.0014 |     350 |      1 |  ← 해시 테이블 (Driving) (ROWS 350)
|  5 |     INDEX (RANGE SCAN)  | IX_T_CUST_CD      |  350 | 00:00:00.0000 |       2 |      1 |  ← 해시 테이블 (Driving) (ROWS 350)
|  6 |    TABLE ACCESS (ROWID) | T_ORDER1          |  700 | 00:00:00.0038 |     700 |    350 |  ← 해시 테이블 (Driving) (ROWS 350)
|  7 |     INDEX (RANGE SCAN)  | IX_T_ORDER1_ID_DY |  700 | 00:00:00.0039 |     379 |    350 |  ← 해시 테이블 (Driving) (ROWS 350)
|  8 |   TABLE ACCESS (FULL)   | T_ORDER_STA1      |    1 | 00:00:00.0085 |      14 |      1 |
----------------------------------------------------------------------------------------------

Predicate Information:
2 - access: ("O"."ORDER_STA_CD" = "S"."ORDER_STA_CD") (1.000)
5 - access: ("C"."CUST_CD" = 'z0005') (0.004)
7 - access: ("O"."CUST_ID" = "C"."CUST_ID")
            AND ("O"."ORDER_DY" >= '20230901')
            AND ("O"."ORDER_DY" <= '20231030') (0.000 * 0.090 * 1,000)
8 - filter: ("S"."ORDER_STA" = '구매확정')
            AND ("S"."CM" IS NOT NULL) (0.200 *

INDEX JOIN 서브트리 (Id 3 ~ 7) 가 700 건짜리 빌드 입력으로 해시 테이블이 되었고, 1 건만 나오는 T_ORDER_STA1 (Id 8) 이 프로브 측이 됩니다. 작은 집합과 큰 집합의 역할이 뒤바뀐 상태입니다.

수정 쿼리 — SWAP_JOIN_INPUTS(S) 추가

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT /*+ LEADING(C O S) USE_NL(O) USE_HASH(S)
           INDEX(C IX_T_CUST_CD) INDEX(O IX_T_ORDER1_ID_DY) FULL(S) SWAP_JOIN_INPUTS(S) */
       *
  FROM T_CUST         C,
       T_ORDER1       O,
       T_ORDER_STA1   S
 WHERE C.CUST_CD = 'z005'
   AND C.CUST_ID = O.CUST_ID
   AND O.ORDER_DY BETWEEN '20230901' AND '20231030'
   AND O.ORDER_STA_CD = S.ORDER_STA_CD
   AND S.ORDER_STA = '구매확정'
   AND S.CM IS NOT NULL
 ORDER BY O.ORDER_DY, C.CUST_ID;

실행 계획

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

----------------------------------------------------------------------------------------------
| ID | Operation               | Name              | Rows | Elaps. Time   | CR Gets | Starts |
----------------------------------------------------------------------------------------------
|  1 | ORDER BY (SORT)         |                   |  700 | 00:00:00.0008 |       0 |      1 |
|  2 |  HASH JOIN              |                   |  700 | 00:00:00.0002 |       0 |      1 |
|  3 |   TABLE ACCESS (FULL)   | T_ORDER_STA1      |    1 | 00:00:00.0001 |      14 |      1 |  ← 해시 테이블 (Driving) (ROWS 1)
|  4 |   INDEX JOIN            |                   |  700 | 00:00:00.0010 |       0 |      1 |
|  5 |    TABLE ACCESS (ROWID) | T_CUST            |  350 | 00:00:00.0024 |     350 |      1 |
|  6 |     INDEX (RANGE SCAN)  | IX_T_CUST_CD      |  350 | 00:00:00.0000 |       2 |      1 |
|  7 |    TABLE ACCESS (ROWID) | T_ORDER1          |  700 | 00:00:00.0045 |     700 |    350 |
|  8 |     INDEX (RANGE SCAN)  | IX_T_ORDER1_ID_DY |  700 | 00:00:00.0045 |     379 |    350 |
----------------------------------------------------------------------------------------------

Predicate Information:
2 - access: ("S"."ORDER_STA_CD" = "O"."ORDER_STA_CD") (1.000)
3 - filter: ("S"."ORDER_STA" = '구매확정')
            AND ("S"."CM" IS NOT NULL) (0.200 *
6 - access: ("C"."CUST_CD" = 'z0005') (0.004)
8 - access: ("O"."CUST_ID" = "C"."CUST_ID")
            AND ("O"."ORDER_DY" >= '20230901')
            AND ("O"."ORDER_DY" <= '20231030') (0.000 * 0.090 * 1,000)

T_ORDER_STA1 (Id 3, 1 건) 이 빌드 입력으로 올라가 작은 해시 테이블이 만들어졌고, INDEX JOIN 서브트리 (Id 4 ~ 8) 의 700 건이 프로브 측으로 내려갔습니다.
작은 쪽이 빌드, 큰 쪽이 프로브 — Hash Join 의 정석 구도입니다.

LEADING 으로 조인 순서를 고정하면서도 빌드/프로브 역할만 따로 뒤집고 싶을 때 SWAP_JOIN_INPUTS 가 답입니다. 작은 집합을 해시 테이블로 유지하는 것이 Hash Join 비용 최소화의 핵심입니다.

SQL Tuning, 케이스 스터디
oracle hash-join swap-join-inputs build-input driving-table
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.