JOIN-054

반품 접수 화면이 주문 상세 세 줄의 그때 가격을 찾는 데 1,200블록을 읽는다

중급SELECT한빛유통204 / 700

상황

고객센터의 「반품 접수」 화면은 주문번호를 넣으면 주문 상세 1 ~ 3줄과 줄마다 주문 시각의 판매가(최저가 연동 가격)를 보여 주고, 판매가와 실제 결제 단가의 차이로 환불액을 계산한다. 상담원이 하루 2천 번쯤 연다. 가격 이력이 날마다 쌓이면서 이 화면이 조금씩 무거워져, 요즘은 주문 한 건에 1,200블록 넘게 읽는다. 아래는 2024-12-08 14:22에 들어온 주문(상세 3줄)으로 잰 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_DT는 시분초 있음 / T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3줄
T_ITM_PRC_LOG 상품 판매가 이력 (이 문제에서 쓰는 파생 테이블, 선분이력): ITM_ID · ST_DTM · ED_DTM (다음 구간의 시작 시각, 이 시각 미만까지) · SLE_PRC · 최저가 연동 배치가 2023-01-01부터 매일 04시에 상품마다 새 구간을 연다 · 73,100행 · 410블록 · 상품마다 약 731구간
인덱스 IX_ITM_PRC_LOG (ITM_ID, ST_DTM): BLEVEL 1 · 리프 약 230블록(상품 하나에 약 2.3블록) · 날마다 100행씩 쌓여서 상품 하나의 구간은 테이블 거의 전체에 흩어져 있다
구간은 겹치거나 끊기지 않는다(매일 아침 점검으로 확인한다) · 통계 최신

원본 SQL

sql

SELECT d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC, h.SLE_PRC AS LIST_PRC
  FROM T_ORD o, T_ORD_DET d, T_ITM_PRC_LOG h
 WHERE o.ORD_SEQ = :v_ord
   AND d.ORD_SEQ = o.ORD_SEQ
   AND h.ITM_ID  = d.ITM_ID
   AND h.ST_DTM <= o.ORD_DT
   AND h.ED_DTM >  o.ORD_DT
 ORDER BY d.ORD_DET_NO

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |      3 |00:00:00.03 |    1247 |
|   1 |  SORT ORDER BY                         |                |      1 |      2 |      3 |00:00:00.03 |    1247 |
|   2 |   NESTED LOOPS                         |                |      1 |      2 |      3 |00:00:00.03 |    1247 |
|   3 |    NESTED LOOPS                        |                |      1 |    730 |   2128 |00:00:00.01 |      17 |
|   4 |     NESTED LOOPS                       |                |      1 |      2 |      3 |00:00:00.01 |       7 |
|   5 |      TABLE ACCESS BY INDEX ROWID       | T_ORD          |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  6 |       INDEX UNIQUE SCAN                | PK_T_ORD       |      1 |      1 |      1 |00:00:00.01 |       2 |
|   7 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_DET     |      1 |      2 |      3 |00:00:00.01 |       4 |
|*  8 |       INDEX RANGE SCAN                 | PK_T_ORD_DET   |      1 |      2 |      3 |00:00:00.01 |       2 |
|*  9 |     INDEX RANGE SCAN                   | IX_ITM_PRC_LOG |      3 |    365 |   2128 |00:00:00.01 |      10 |
|* 10 |    TABLE ACCESS BY INDEX ROWID         | T_ITM_PRC_LOG  |   2128 |      1 |      3 |00:00:00.02 |    1230 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("O"."ORD_SEQ"=:V_ORD)
   8 - access("D"."ORD_SEQ"=:V_ORD)
   9 - access("H"."ITM_ID"="D"."ITM_ID" AND "H"."ST_DTM"<="O"."ORD_DT")
  10 - filter("H"."ED_DTM">"O"."ORD_DT")

문제

상세 3줄의 가격 3개를 얻는 데 1,247블록을 읽은 까닭을 9번과 10번 줄로 설명하고, 가격 이력이 쌓이면 이 일량이 어떻게 변하는지 쓰시오.
인덱스를 새로 만들지 않고, 상세 한 줄마다 가격 구간을 한 건만 읽고 멈추도록 SQL을 고치시오. 이 방법이 맞으려면 데이터에 어떤 전제가 필요한지도 쓰시오.
인덱스를 (ITM_ID, ED_DTM, ST_DTM)으로 하나 더 만드는 안과 견주어, 어느 쪽을 고를지 근거와 함께 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상세 한 줄마다 따로 묻는다: 시작 시각이 주문 시각 이전인 구간을 역순으로 읽어 첫 한 건에서 멈춘다
SELECT d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY, d.UNT_PRC,
       CASE WHEN p.ED_DTM > o.ORD_DT THEN p.SLE_PRC END AS LIST_PRC   -- 구간이 끊긴 경우를 대비해 끝도 확인
  FROM T_ORD o
  JOIN T_ORD_DET d ON d.ORD_SEQ = o.ORD_SEQ
 OUTER APPLY (SELECT h.SLE_PRC, h.ED_DTM
                FROM T_ITM_PRC_LOG h
               WHERE h.ITM_ID  = d.ITM_ID
                 AND h.ST_DTM <= o.ORD_DT
               ORDER BY h.ST_DTM DESC
               FETCH FIRST 1 ROW ONLY) p
 WHERE o.ORD_SEQ = :v_ord
 ORDER BY d.ORD_DET_NO

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

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |      1 |        |      3 |00:00:00.01 |      19 |
|   1 |  SORT ORDER BY                         |                 |      1 |      2 |      3 |00:00:00.01 |      19 |
|   2 |   NESTED LOOPS OUTER                   |                 |      1 |      2 |      3 |00:00:00.01 |      19 |
|   3 |    NESTED LOOPS                        |                 |      1 |      2 |      3 |00:00:00.01 |       7 |
|   4 |     TABLE ACCESS BY INDEX ROWID        | T_ORD           |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  5 |      INDEX UNIQUE SCAN                 | PK_T_ORD        |      1 |      1 |      1 |00:00:00.01 |       2 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_DET       |      1 |      2 |      3 |00:00:00.01 |       4 |
|*  7 |      INDEX RANGE SCAN                  | PK_T_ORD_DET    |      1 |      2 |      3 |00:00:00.01 |       2 |
|*  8 |    VIEW                                | VW_LAT_C83A7ED5 |      3 |      1 |      3 |00:00:00.01 |      12 |
|*  9 |     WINDOW NOSORT STOPKEY              |                 |      3 |      1 |      3 |00:00:00.01 |      12 |
|  10 |      TABLE ACCESS BY INDEX ROWID       | T_ITM_PRC_LOG   |      3 |    365 |      3 |00:00:00.01 |      12 |
|* 11 |       INDEX RANGE SCAN DESCENDING      | IX_ITM_PRC_LOG  |      3 |    365 |      3 |00:00:00.01 |       9 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("O"."ORD_SEQ"=:V_ORD)
   7 - access("D"."ORD_SEQ"=:V_ORD)
   8 - filter("from$_subquery$_007"."rowlimit_$$_rownumber"<=1)
   9 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("H"."ST_DTM") DESC )<=1)
  11 - access("H"."ITM_ID"="D"."ITM_ID" AND "H"."ST_DTM"<="O"."ORD_DT")

모범 답안 서술

9번 줄의 access 조건은 "H"."ST_DTM"<="O"."ORD_DT" 하나다. 2024-12-08 주문이면 시작 시각이 그 이전인 구간은 2023년 1월부터 상품마다 약 709개라, 상세 3줄 × 709 = 2,128개 항목을 읽었다. 종료 시각 조건은 인덱스에 없으므로 10번 줄에서 테이블에 2,128번 가서 filter("H"."ED_DTM">"O"."ORD_DT")로 거르고 3행만 남겼다. 상품 하나의 구간이 테이블 전체에 흩어져 있어서 테이블에서만 1,230블록을 읽었다. 가격 이력은 날마다 상품마다 한 구간씩 늘어나므로, 같은 화면이라도 최근 주문일수록, 그리고 해가 갈수록 무거워진다. 구간이 겹치지 않는다면 시작 시각이 주문 시각 이전인 구간 가운데 가장 늦게 시작한 한 건이 답이다. 인덱스가 이미 (ITM_ID, ST_DTM) 순서이므로 주문 시각부터 거꾸로(RANGE SCAN DESCENDING) 읽어 첫 항목에서 멈추면 된다. 그런데 조인 문장에는 '상세 한 줄마다 한 건에서 멈추기'를 쓸 수 없다. 그래서 OUTER APPLY 안에 ORDER BY h.ST_DTM DESC FETCH FIRST 1 ROW ONLY를 두어 상세 한 줄마다 따로 묻는다(9번 줄 WINDOW NOSORT STOPKEY). 줄마다 인덱스 3블록과 테이블 1블록이면 되고, 이 일량은 인덱스 구성과도, 주문이 얼마나 오래됐는지와도 상관이 없다. Buffers는 1,247 → 19로 준다(하루 2천 번이면 약 249만 → 3만 8천). 이 방법은 구간이 겹치지 않는다는 전제에서만 맞다. 겹치면 어느 구간이 첫 건이 될지 정해지지 않는다. 그래서 매일 아침의 겹침 점검이 이 화면의 전제다. 구간이 끊긴 곳에 주문이 걸리면 첫 건이 이미 끝난 구간일 수 있으므로, 찾은 구간의 ED_DTM이 주문 시각보다 뒤인지 한 번 더 확인해 아니면 빈칸으로 둔다. (ITM_ID, ED_DTM, ST_DTM) 인덱스를 더 만드는 안도 조인 문장 그대로 빨라진다. 종료 시각이 주문 시각 이후인 구간부터 읽으므로 최근 주문이면 몇 항목으로 끝난다. 하지만 오래된 주문일수록 그 뒤의 구간을 다 읽어야 하고(2023년 주문이면 수백 항목), 날마다 100행씩 들어오는 이력에 인덱스 유지 비용이 하나 더 붙는다. 인덱스 없이 주문 시점과 상관없이 한 건에서 멈추는 쪽을 고른다.

해설

스칼라 서브쿼리로 쓴다면: 가격 한 칸만 필요하므로 (SELECT h.SLE_PRC FROM T_ITM_PRC_LOG h WHERE h.ITM_ID = d.ITM_ID AND h.ST_DTM <= o.ORD_DT ORDER BY h.ST_DTM DESC FETCH FIRST 1 ROW ONLY)로 써도 같은 일량이다. 칸이 둘 이상이면 서브쿼리를 칸마다 반복하지 말고 OUTER APPLY 하나로 가져온다.
KEEP은 멈추지 못한다: MAX(h.SLE_PRC) KEEP (DENSE_RANK LAST ORDER BY h.ST_DTM)도 답은 맞지만, 집계라서 조건을 만족하는 709개 항목을 다 읽은 뒤에 고른다. 한 건에서 멈추는 것은 정렬된 인덱스와 STOPKEY의 조합이다.
대상이 많으면 다르다: 한 달 치 주문 수만 줄에 그때 가격을 붙이는 리포트라면 줄마다 따로 묻는 왕복이 줄 수만큼 생긴다. 그때는 해시 조인으로 한 번에 맞추고, 조회 기간에 걸친 구간만 build로 올린다.
겹침을 아예 못 넣게 하려면 (ITM_ID, ST_DTM)에 유니크 제약을 둔다. 지금의 일반 인덱스를 유니크 인덱스로 바꾸면 같은 인덱스가 이 화면과 무결성을 함께 지킨다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황