JOIN-072

연말 매출 요약 배치를 두고 개발자는 해시 조인이, DBA는 NL 조인이 빠르다고 한다

실전INSERT한빛유통222 / 700

상황

정보기획팀의 연말 배치는 한 해 동안 A, B 등급 고객이 산 신선과 냉장 상품의 매출을 등급, 지역, 상품 유형별로 묶어 요약 테이블에 넣는다. 개발자는 FROM 절 순서대로 LEADING을 적고 다섯 테이블을 모두 해시로 붙였다(A안). DBA는 "등급 조건이 10%만 남기니 고객부터 NL로 가야 한다"며 시험 DB에서 B안을 돌려 보았다. 박팀장은 두 안과, 다른 안이 있다면 그것까지 숫자로 비교해 하나를 고르고 그 결정이 언제 바뀌어야 하는지도 적어 달라고 했다. 아래는 A안으로 2024년을 돌린 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_CUS_DT (CUS_ID, ORD_DT) · 2024년 주문 50,000건(테이블 전체에 흩어져 있다), 그중 A, B 등급 고객의 주문 5,003건
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 2024년 주문의 상세 99,934줄, A, B 등급 몫 9,968줄
M_ITM: 100행 · 5블록 · ITM_TP 4종(신선 'FRESH'과 냉장 'CHILL'이 합쳐 50개) / M_CUS: 2,000행 · 30블록 · A, B 등급 200명 / M_RGN: 8행 · 4블록
B안(DBA, 시험 DB에서 블록이 모두 캐시에 올라온 상태로 잼): LEADING(c o d i r) USE_NL(o d) · Buffers 20,268 · 0.11초 · T_ORD를 IX_T_ORD_CUS_DT로, 주문상세를 PK로 각각 5,003번 찾는다
운영의 연말 배치 시간대에는 T_ORD와 T_ORD_DET 블록 대부분이 버퍼 캐시에 없다 · 통계 최신

원본 SQL

sql

INSERT INTO T_SLS_GD_RGN_TP (BAS_YY, CUS_GD, RGN_NM, ITM_TP, DET_CNT, SLE_AMT)
SELECT /*+ LEADING(o d i c r) USE_HASH(d i c r) */
       :v_yy, c.CUS_GD, r.RGN_NM, i.ITM_TP, COUNT(*), SUM(d.ORD_QTY * d.UNT_PRC)
  FROM T_ORD o, T_ORD_DET d, M_ITM i, M_CUS c, M_RGN r
 WHERE o.ORD_DT >= TO_DATE(:v_yy || '0101', 'YYYYMMDD')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_yy || '0101', 'YYYYMMDD'), 12)
   AND d.ORD_SEQ = o.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
   AND i.ITM_TP IN ('FRESH', 'CHILL')
   AND c.CUS_ID  = o.CUS_ID
   AND c.CUS_GD IN ('A', 'B')
   AND r.RGN_ID  = c.RGN_ID
 GROUP BY c.CUS_GD, r.RGN_NM, i.ITM_TP

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |                 |      1 |        |      0 |00:00:00.27 |    1856 |           |
|   1 |  LOAD TABLE CONVENTIONAL  | T_SLS_GD_RGN_TP |      1 |        |      0 |00:00:00.27 |    1856 |           |
|   2 |   HASH GROUP BY           |                 |      1 |     32 |     32 |00:00:00.26 |    1831 | 1156K (0) |
|*  3 |    HASH JOIN              |                 |      1 |   5000 |   4987 |00:00:00.26 |    1831 | 1290K (0) |
|   4 |     TABLE ACCESS FULL     | M_RGN           |      1 |      8 |      8 |00:00:00.01 |       4 |           |
|*  5 |     HASH JOIN             |                 |      1 |   5000 |   4987 |00:00:00.26 |    1827 | 1347K (0) |
|*  6 |      TABLE ACCESS FULL    | M_CUS           |      1 |    200 |    200 |00:00:00.01 |      30 |           |
|*  7 |      HASH JOIN            |                 |      1 |  50000 |  49871 |00:00:00.24 |    1797 | 1236K (0) |
|*  8 |       TABLE ACCESS FULL   | M_ITM           |      1 |     50 |     50 |00:00:00.01 |       5 |           |
|*  9 |       HASH JOIN           |                 |      1 |   100K |  99934 |00:00:00.19 |    1792 | 4417K (0) |
|* 10 |        TABLE ACCESS FULL  | T_ORD           |      1 |  50000 |  50000 |00:00:00.01 |     896 |           |
|  11 |        TABLE ACCESS FULL  | T_ORD_DET       |      1 |   200K |   200K |00:00:00.04 |     896 |           |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("R"."RGN_ID"="C"."RGN_ID")
   5 - access("C"."CUS_ID"="O"."CUS_ID")
   6 - filter(("C"."CUS_GD"='A' OR "C"."CUS_GD"='B'))
   7 - access("I"."ITM_ID"="D"."ITM_ID")
   8 - filter(("I"."ITM_TP"='CHILL' OR "I"."ITM_TP"='FRESH'))
   9 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
  10 - filter(("O"."ORD_DT">=TO_DATE(:V_YY||'0101','YYYYMMDD') AND "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YY||'0101','YYYYMMDD'),12)))

문제

A안의 실행계획에서 조인마다 흐르는 행 수를 줄 번호로 짚고, 일량이 어디서 나오는지 Buffers와 A-Rows로 설명하시오.
A안, B안, 그리고 고객 조건을 먼저 적용하는 해시 조인 안(C안)을 Buffers, 조인에 흐르는 행 수, 물리 I/O 위험으로 비교하고, 하나를 골라 SQL을 작성하시오.
고른 안이 틀린 답이 되는 조건(등급 조건이나 기간이 바뀔 때)을 쓰고, 이 배치에 힌트를 남길지 판단하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- C안 : 해시 조인은 그대로, 걸러 내는 고객(등급)과 지역을 먼저 맞대어 200명을 만든 뒤 주문 → 주문상세 → 상품 순으로 붙인다
-- 통계가 맞으면 힌트 없이도 이 순서가 나온다(시험 DB 에서 확인). 남긴다면 전제를 힌트 대장에 적는다
INSERT INTO T_SLS_GD_RGN_TP (BAS_YY, CUS_GD, RGN_NM, ITM_TP, DET_CNT, SLE_AMT)
SELECT /*+ LEADING(c r o d i) USE_HASH(r o d i) */
       :v_yy, c.CUS_GD, r.RGN_NM, i.ITM_TP, COUNT(*), SUM(d.ORD_QTY * d.UNT_PRC)
  FROM T_ORD o, T_ORD_DET d, M_ITM i, M_CUS c, M_RGN r
 WHERE o.ORD_DT >= TO_DATE(:v_yy || '0101', 'YYYYMMDD')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_yy || '0101', 'YYYYMMDD'), 12)
   AND d.ORD_SEQ = o.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
   AND i.ITM_TP IN ('FRESH', 'CHILL')
   AND c.CUS_ID  = o.CUS_ID
   AND c.CUS_GD IN ('A', 'B')
   AND r.RGN_ID  = c.RGN_ID
 GROUP BY c.CUS_GD, r.RGN_NM, i.ITM_TP

개선 후 실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT          |                 |      1 |        |      0 |00:00:00.09 |    1856 |           |
|   1 |  LOAD TABLE CONVENTIONAL  | T_SLS_GD_RGN_TP |      1 |        |      0 |00:00:00.09 |    1856 |           |
|   2 |   HASH GROUP BY           |                 |      1 |     32 |     32 |00:00:00.08 |    1831 | 1156K (0) |
|*  3 |    HASH JOIN              |                 |      1 |   5000 |   4987 |00:00:00.08 |    1831 | 1236K (0) |
|*  4 |     TABLE ACCESS FULL     | M_ITM           |      1 |     50 |     50 |00:00:00.01 |       5 |           |
|*  5 |     HASH JOIN             |                 |      1 |  10000 |   9968 |00:00:00.07 |    1826 | 1402K (0) |
|*  6 |      HASH JOIN            |                 |      1 |   5000 |   5003 |00:00:00.02 |     930 | 1347K (0) |
|*  7 |       HASH JOIN           |                 |      1 |    200 |    200 |00:00:00.01 |      34 | 1290K (0) |
|   8 |        TABLE ACCESS FULL  | M_RGN           |      1 |      8 |      8 |00:00:00.01 |       4 |           |
|*  9 |        TABLE ACCESS FULL  | M_CUS           |      1 |    200 |    200 |00:00:00.01 |      30 |           |
|* 10 |       TABLE ACCESS FULL   | T_ORD           |      1 |  50000 |  50000 |00:00:00.01 |     896 |           |
|  11 |      TABLE ACCESS FULL    | T_ORD_DET       |      1 |   200K |   200K |00:00:00.04 |     896 |           |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."ITM_ID"="D"."ITM_ID")
   4 - filter(("I"."ITM_TP"='CHILL' OR "I"."ITM_TP"='FRESH'))
   5 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   6 - access("C"."CUS_ID"="O"."CUS_ID")
   7 - access("R"."RGN_ID"="C"."RGN_ID")
   9 - filter(("C"."CUS_GD"='A' OR "C"."CUS_GD"='B'))
  10 - filter(("O"."ORD_DT">=TO_DATE(:V_YY||'0101','YYYYMMDD') AND "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YY||'0101','YYYYMMDD'),12)))

모범 답안 서술

A안의 일량: Buffers 1,831은 다섯 테이블을 한 번씩 읽은 몫이다. 일량은 조인에 흐르는 행에서 나온다. 9번 줄이 2024년 주문 50,000건을 build로 올리고(4,417K) 주문상세 20만 줄을 대어 99,934줄을 만들고, 7번 줄이 그 10만 줄을 상품 유형으로 걸러 49,871줄, 5번 줄이 고객 등급으로 걸러 4,987줄을 남긴다. 등급 조건이 맨 뒤에 있어서 버릴 행 9만 5천 줄을 두 번의 해시 조인에 흘린 뒤에야 버린다. 0.27초의 대부분이 9번과 7번 줄이다. B안: 고객 200명에서 출발해 주문 5,003건, 상세 9,968줄만 다루므로 조인에 흐르는 행은 가장 적다. 하지만 주문과 상세를 인덱스로 5,003번씩 찾아가 Buffers가 20,268로 A안의 11배다. 시험 DB의 0.11초는 블록이 모두 캐시에 있을 때의 숫자다. 연말 배치 시간대처럼 캐시에 없으면 이 2만 블록의 상당수가 단일 블록 디스크 읽기가 되어 수 초로 늘 수 있다. C안(권함): 해시 조인은 그대로 두고 순서만 바꾼다. 고객(등급 조건)과 지역을 먼저 맞대어 200명을 만들고, 그 200명을 build로 삼아 2024년 주문을 대어 5,003건만 남긴다. 그다음 주문상세를 대어 9,968줄, 상품 유형으로 거르면 4,987줄이다. 테이블은 A안처럼 한 번씩 멀티블록으로 읽으므로 Buffers는 1,831 그대로이고 물리 I/O도 FULL 두 번이다. 조인에 흐르는 행은 99,934 → 9,968줄, 가장 큰 해시 영역은 4,417K → 1,402K, 시간은 0.27 → 0.09초다. 결정이 바뀌는 조건: 등급 조건이 A 등급만(1%, 20명)이 되면 주문은 약 500건이다. 그때 B안처럼 고객에서 NL로 가면 2천 블록 남짓이라 C안(1,831)과 비슷해지고, 기간까지 한 달로 좁아지면 NL이 이긴다. 반대로 전 등급이면 등급 조건이 거르는 행이 없어서 A안과 C안의 차이도 사라진다. 그러므로 이 배치에는 힌트로 순서를 박지 않는다. 통계가 맞으면 옵티마이저는 C안을 고른다(시험 DB에서 힌트를 떼고 확인). 꼭 남겨야 한다면 힌트 유효기간 대장에 'A, B 등급, 연 단위, 2024년 주문 5만 건' 같은 전제와 재검토 조건을 함께 적는다.

해설

Buffers만 보면 A안과 C안은 같다: 같은 테이블을 같은 방식으로 한 번씩 읽기 때문이다. 차이는 조인 줄의 A-Rows와 Used-Mem, 그리고 시간에 있다. 계획을 비교할 때 Buffers 하나만 보면 이 차이를 놓친다.
B안이 맞는 곳: 화면처럼 같은 SQL이 하루 수천 번 돌고 블록이 캐시에 있으면 NL의 2만 블록도 빠르다. 배치처럼 한 번 크게 도는 SQL은 멀티블록으로 한 번 읽는 해시 쪽이 물리 I/O에서 유리하다(교재 06화 손익분기점).
조인 순서를 정하는 기준: 테이블 크기가 아니라 '이 조인 뒤에 몇 행이 남는가'다. 다섯 테이블이면 순서의 경우가 120가지지만, 행을 가장 많이 거르는 조건부터 적용한다는 기준 하나로 후보가 크게 준다.
힌트의 유효기간: JOIN-001의 USE_NL처럼 오늘의 규모와 조건에서 내린 판단은 조건이 바뀌면 틀린다. 힌트를 남기는 순간 그 전제를 다시 확인할 일정도 함께 생긴다(교재 06화 힌트 유효기간 대장).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황