JOIN-015

상품 루프를 한 문장으로 바꿨는데 당일 상품별 집계가 한 번에 33만 블록을 읽는다

중급INSERT한빛유통165 / 700

상황

「당일 상품별 판매 현황」은 5분마다 오늘 주문을 상품별로 묶어 요약 테이블에 다시 적재한다(하루 288번). 예전에는 상품 100개를 하나씩 돌며 집계했는데, 지난달 조민우가 INSERT ... SELECT 한 문장으로 바꿨다. 조민우는 "상품별 집계라서 상품부터 읽도록 힌트를 달았다"라고 한다. 적재 한 번은 1초 남짓이지만, 하루 288번 돌면서 서버의 논리 읽기 상위에 올라 있다. 아래는 2024-06-14 한 번의 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT) · 하루 137건이고, 하루치 주문은 테이블 전체에 흩어져 있다
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · IX_T_ORD_DET_ITM (ITM_ID) (상품 화면용) · 상품마다 2,000줄 · 2024-06-14 주문상세는 274줄 (상품 30종)
M_ITM: 100행 · 5블록 · PK_M_ITM (ITM_ID)
요약 테이블 T_ITM_DAY_SLS (BAS_DT, ITM_ID, ORD_CNT, ORD_QTY, ORD_AMT): 적재 전에 그날 행을 지운다 · 인덱스 없음 · 통계 최신

원본 SQL

sql

INSERT INTO T_ITM_DAY_SLS (BAS_DT, ITM_ID, ORD_CNT, ORD_QTY, ORD_AMT)
SELECT /*+ LEADING(m d o) USE_NL(d o) */
       :v_dt, m.ITM_ID, COUNT(DISTINCT o.ORD_SEQ), SUM(d.ORD_QTY), SUM(d.ORD_QTY * d.UNT_PRC)
  FROM M_ITM m, T_ORD_DET d, T_ORD o
 WHERE d.ITM_ID  = m.ITM_ID
   AND o.ORD_SEQ = d.ORD_SEQ
   AND o.ORD_DT >= :v_dt
   AND o.ORD_DT <  :v_dt + 1
 GROUP BY m.ITM_ID

실행계획 학습용 예시 · 행 수는 실데이터

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |                  |      1 |        |      0 |00:00:01.21 |    331K |
|   1 |  LOAD TABLE CONVENTIONAL          | T_ITM_DAY_SLS    |      1 |        |      0 |00:00:01.21 |    331K |
|   2 |   HASH GROUP BY                   |                  |      1 |    100 |     30 |00:00:01.21 |    331K |
|   3 |    NESTED LOOPS                   |                  |      1 |    274 |    274 |00:00:01.20 |    331K |
|   4 |     NESTED LOOPS                  |                  |      1 |   200K |   200K |00:00:00.52 |    131K |
|   5 |      NESTED LOOPS                 |                  |      1 |   200K |   200K |00:00:00.31 |   90205 |
|   6 |       INDEX FULL SCAN             | PK_M_ITM         |      1 |    100 |    100 |00:00:00.01 |       1 |
|   7 |       TABLE ACCESS BY INDEX ROWID | T_ORD_DET        |    100 |   2000 |   200K |00:00:00.28 |   90204 |
|*  8 |        INDEX RANGE SCAN           | IX_T_ORD_DET_ITM |    100 |   2000 |   200K |00:00:00.04 |     604 |
|*  9 |      INDEX UNIQUE SCAN            | PK_T_ORD         |   200K |      1 |   200K |00:00:00.15 |   40612 |
|* 10 |     TABLE ACCESS BY INDEX ROWID   | T_ORD            |   200K |      1 |    274 |00:00:00.62 |    200K |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("D"."ITM_ID"="M"."ITM_ID")
   9 - access("O"."ORD_SEQ"="D"."ORD_SEQ")
  10 - filter(("O"."ORD_DT">=:V_DT AND "O"."ORD_DT"<:V_DT+1))

문제

6번부터 10번 줄의 Starts와 A-Rows를 따라가며, 그날 주문상세 274줄을 얻으려고 몇 행을 거쳤는지 설명하시오.
조인 순서를 다시 정해 개선 SQL을 작성하시오. "상품별로 묶으니 상품부터 읽는다"는 판단이 왜 틀렸는지 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 오늘 주문 137건에서 출발: 주문 → 주문상세(PK) → 상품(PK). 묶는 것은 조인이 끝난 뒤에 한다
INSERT INTO T_ITM_DAY_SLS (BAS_DT, ITM_ID, ORD_CNT, ORD_QTY, ORD_AMT)
SELECT /*+ LEADING(o d m) USE_NL(d m) */
       :v_dt, m.ITM_ID, COUNT(DISTINCT o.ORD_SEQ), SUM(d.ORD_QTY), SUM(d.ORD_QTY * d.UNT_PRC)
  FROM T_ORD o, T_ORD_DET d, M_ITM m
 WHERE o.ORD_DT >= :v_dt
   AND o.ORD_DT <  :v_dt + 1
   AND d.ORD_SEQ = o.ORD_SEQ
   AND m.ITM_ID  = d.ITM_ID
 GROUP BY m.ITM_ID

개선 후 실행계획 학습용 예시 · 행 수는 실데이터

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                         |               |      1 |        |      0 |00:00:00.01 |     559 |
|   1 |  LOAD TABLE CONVENTIONAL                 | T_ITM_DAY_SLS |      1 |        |      0 |00:00:00.01 |     559 |
|   2 |   HASH GROUP BY                          |               |      1 |    100 |     30 |00:00:00.01 |     559 |
|   3 |    NESTED LOOPS                          |               |      1 |    274 |    274 |00:00:00.01 |     559 |
|   4 |     NESTED LOOPS                         |               |      1 |    274 |    274 |00:00:00.01 |     554 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD         |      1 |    137 |    137 |00:00:00.01 |     139 |
|*  6 |       INDEX RANGE SCAN                   | IX_T_ORD_DT   |      1 |    137 |    137 |00:00:00.01 |       2 |
|   7 |      TABLE ACCESS BY INDEX ROWID         | T_ORD_DET     |    137 |      2 |    274 |00:00:00.01 |     415 |
|*  8 |       INDEX RANGE SCAN                   | PK_T_ORD_DET  |    137 |      2 |    274 |00:00:00.01 |     278 |
|*  9 |     INDEX UNIQUE SCAN                    | PK_M_ITM      |    274 |      1 |    274 |00:00:00.01 |       5 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("O"."ORD_DT">=:V_DT AND "O"."ORD_DT"<:V_DT+1)
   8 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   9 - access("M"."ITM_ID"="D"."ITM_ID")

모범 답안 서술

6번부터 10번 줄을 따라가면 PK_M_ITM에서 상품 100개를 읽고(6번), 상품마다 IX_T_ORD_DET_ITM으로 그 상품의 주문상세 2,000줄을 읽었다(7번 A-Rows 200K). 상품 100개 × 2,000줄이니 결국 주문상세 전체 20만 줄이다. 그 20만 줄마다 PK_T_ORD로 주문을 찾아가(9번, 10번 Starts 200K) 날짜 조건으로 걸렀고, 오늘 주문에 속한 274줄만 남았다. 20만 번 찾아간 주문 테이블에서만 200K블록, 전체 Buffers는 약 33만이다. 하루 288번이면 약 9,500만 블록이다. '상품별로 묶는다'는 것은 GROUP BY의 기준일 뿐 어느 테이블을 먼저 읽을지와는 별개다. GROUP BY는 조인이 끝난 결과를 묶으므로, 조인 순서는 조건을 적용했을 때 남는 행 수로 정한다. 이 SQL에서 가장 많이 거르는 조건은 날짜(오늘 137건)이고, 상품 쪽에는 거르는 조건이 없다. 그래서 오늘 주문 137건에서 출발해 주문마다 PK_T_ORD_DET로 주문상세 274줄을 찾고, 상품은 그다음에 PK로 붙인다. 고친 SQL의 Buffers는 331K → 559다. 루프 시절의 '상품 하나 → 그 상품의 주문상세 → 주문' 순서를 한 문장으로 옮기면서 그대로 가져온 것이 원인이었다. 힌트를 떼거나 해시 조인으로 T_ORD_DET를 한 번 FULL로 읽는 안(896 + 139, 약 1,040블록)도 원본보다 300배 낫다. 다만 하루 137건이면 NL이 559블록으로 더 싸다. 한 달을 한 번에 묶는 배치라면 해시 조인이 낫다.

해설

루프의 흔적: 상품 100개를 하나씩 돌던 시절에는 '상품 하나 → 그 상품의 상세 2,000줄 → 주문'이 루프 한 바퀴였다. 문장을 합칠 때는 조인 순서를 처음부터 다시 정한다. 한 문장으로 바꿔도 그 안에서 같은 반복을 하면 일량은 줄지 않는다(교재 15화).
인덱스가 있다는 것은 출발 이유가 아니다: IX_T_ORD_DET_ITM은 상품 하나를 조회하는 화면에서는 맞는 인덱스다. 이 배치에는 상품 조건이 없으므로 그 인덱스에서 출발할 이유가 없다.
COUNT(DISTINCT): 한 주문에 같은 상품이 두 줄 들어갈 수 있어 주문 수는 COUNT(DISTINCT o.ORD_SEQ)로 센다. 순서를 바꿔도 결과가 같은지 요약 테이블의 건수와 금액 합계로 대조한다(교재 08화 §10).
M_ITM은 상품이 마스터에 있는지만 확인한다. 고아 상품이 없다는 것이 보장되면 조인을 빼도 되지만, 그 판단은 이 문제의 범위 밖이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황