JOIN-049

월 정가 대조 리포트가 157행짜리 가격표를 4만 6천 번 찾아간다

기초SELECT유통199 / 700

상황

영업관리팀의 「월 정가 대조」는 상품마다 그달 판매 수량과 실제 판매 금액, 판매일 당시 정가로 계산한 금액을 나란히 내서 할인이 얼마나 나갔는지 본다. 월초에는 달을 바꿔 가며 하루 수십 번 돌리고, 감사 기간에는 2년 치를 한 달씩 몰아서 돌린다. 2023년 6월 15일에 판매가가 바뀐 상품이 있어서, 정가는 판매일마다 그날 적용되던 값을 써야 한다. 개발자는 "가격표가 5블록이라 몇 번을 읽든 상관없다"라고 한다. 아래는 2023년 6월로 돌린 실행계획이다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_NO는 'YYYYMMDD-NNNN' 모양이고 앞 8자리가 판매일자다 · 2023년 6월 45,935줄(CF11_PK 리프 약 181블록, 테이블 약 713블록에 모여 있다) · 그달 팔린 상품 88개
CF05 상품가격 (점이력): 157행 · 5블록 · CF05_PK (ITM_NO, UPRC_KN_CD, APL_DT) · 리프 블록 하나 · APL_DT VARCHAR2(8)
이 문제의 CF05: 판매가(UPRC_KN_CD = '10') 127행 (상품 89개의 '20220101' 89행 + 38개 상품의 '20230615' 38행), 나머지 30행은 발주단가('11') · 통계 최신
바인드 :v_from = '20230601' · :v_to = '20230701'

원본 SQL

sql

SELECT d.ITM_NO,
       SUM(d.SLE_QTY)              AS sle_qty,
       SUM(d.SLE_QTY * d.SLE_UPRC) AS sle_amt,
       SUM(d.SLE_QTY * p.ITM_UPRC) AS list_amt
  FROM CF11 d, CF05 p
 WHERE d.SLE_NO >= :v_from
   AND d.SLE_NO <  :v_to
   AND p.ITM_NO = d.ITM_NO
   AND p.UPRC_KN_CD = '10'
   AND p.APL_DT = (SELECT MAX(x.APL_DT)
                     FROM CF05 x
                    WHERE x.ITM_NO = d.ITM_NO
                      AND x.UPRC_KN_CD = '10'
                      AND x.APL_DT <= SUBSTR(d.SLE_NO, 1, 8))
 GROUP BY d.ITM_NO

실행계획 학습용 예시 · 판매상품 행 수는 실데이터

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |     88 |00:00:00.31 |  138699 |
|   1 |  HASH GROUP BY                         |         |      1 |     88 |     88 |00:00:00.31 |  138699 |
|   2 |   NESTED LOOPS                         |         |      1 |  45980 |  45935 |00:00:00.27 |  138699 |
|   3 |    NESTED LOOPS                        |         |      1 |  45980 |  45935 |00:00:00.18 |   92764 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF11    |      1 |  45980 |  45935 |00:00:00.02 |     894 |
|*  5 |      INDEX RANGE SCAN                  | CF11_PK |      1 |  45980 |  45935 |00:00:00.01 |     181 |
|*  6 |     INDEX UNIQUE SCAN                  | CF05_PK |  45935 |      1 |  45935 |00:00:00.12 |   91870 |
|   7 |      SORT AGGREGATE                    |         |  45935 |      1 |  45935 |00:00:00.05 |   45935 |
|   8 |       FIRST ROW                        |         |  45935 |      1 |  45935 |00:00:00.04 |   45935 |
|*  9 |        INDEX RANGE SCAN (MIN/MAX)      | CF05_PK |  45935 |      1 |  45935 |00:00:00.03 |   45935 |
|  10 |    TABLE ACCESS BY INDEX ROWID         | CF05    |  45935 |      1 |  45935 |00:00:00.06 |   45935 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("D"."SLE_NO">=:V_FROM AND "D"."SLE_NO"<:V_TO)
   6 - access("P"."ITM_NO"="D"."ITM_NO" AND "P"."UPRC_KN_CD"='10' AND "P"."APL_DT"=(SELECT MAX("X"."APL_DT") FROM "CF05" "X" ...))
   9 - access("X"."ITM_NO"=:B1 AND "X"."UPRC_KN_CD"='10' AND "X"."APL_DT"<=SUBSTR(:B2,1,8))

문제

5블록짜리 CF05에서 13만 7천 블록 넘게 읽은 까닭을 6번 ~ 10번 줄의 Starts로 설명하시오.
판매일 당시 정가라는 결과는 그대로 두고, CF05를 한 번만 읽어 판매상품과 조인하도록 SQL을 고치시오. 경계(가격이 바뀐 날)를 어떻게 처리했는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 점이력 → 구간: 상품마다 다음 적용일을 종료일로 붙인다 (마지막 구간은 '99991231')
SELECT d.ITM_NO,
       SUM(d.SLE_QTY)              AS sle_qty,
       SUM(d.SLE_QTY * d.SLE_UPRC) AS sle_amt,
       SUM(d.SLE_QTY * p.ITM_UPRC) AS list_amt
  FROM CF11 d,
       (SELECT ITM_NO, ITM_UPRC,
               APL_DT AS FR_DT,
               LEAD(APL_DT, 1, '99991231') OVER (PARTITION BY ITM_NO ORDER BY APL_DT) AS TO_DT
          FROM CF05
         WHERE UPRC_KN_CD = '10') p
 WHERE d.SLE_NO >= :v_from
   AND d.SLE_NO <  :v_to
   AND p.ITM_NO = d.ITM_NO
   AND SUBSTR(d.SLE_NO, 1, 8) >= p.FR_DT      -- 시작일 이상
   AND SUBSTR(d.SLE_NO, 1, 8) <  p.TO_DT      -- 다음 적용일 미만
 GROUP BY d.ITM_NO

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

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |     88 |00:00:00.05 |     899 |
|   1 |  HASH GROUP BY                         |         |      1 |     88 |     88 |00:00:00.05 |     899 |
|*  2 |   HASH JOIN                            |         |      1 |   2299 |  45935 |00:00:00.04 |     899 |
|   3 |    VIEW                                |         |      1 |    127 |    127 |00:00:00.01 |       5 |
|   4 |     WINDOW SORT                        |         |      1 |    127 |    127 |00:00:00.01 |       5 |
|*  5 |      TABLE ACCESS FULL                 | CF05    |      1 |    127 |    127 |00:00:00.01 |       5 |
|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF11    |      1 |  45980 |  45935 |00:00:00.02 |     894 |
|*  7 |     INDEX RANGE SCAN                   | CF11_PK |      1 |  45980 |  45935 |00:00:00.01 |     181 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."ITM_NO"="D"."ITM_NO")
       filter((SUBSTR("D"."SLE_NO",1,8)>="P"."FR_DT" AND SUBSTR("D"."SLE_NO",1,8)<"P"."TO_DT"))
   5 - filter("UPRC_KN_CD"='10')
   7 - access("D"."SLE_NO">=:V_FROM AND "D"."SLE_NO"<:V_TO)

모범 답안 서술

CF05는 5블록이지만, 이 SQL은 판매 한 줄마다 CF05를 새로 찾아간다. 9번 줄 INDEX RANGE SCAN (MIN/MAX)가 그 판매일까지의 최신 적용일을 구하고, 6번 줄이 그 적용일로 CF05_PK를 다시 찾고, 10번 줄이 테이블에서 단가를 읽는다. 세 줄의 Starts가 모두 45,935, 곧 6월 판매상품 줄 수와 같다. 한 번에 1블록씩이어도 45,935 × 3 = 137,805블록이 되고, 전체 Buffers 138,699 가운데 99%가 이 몫이다. 테이블이 작아도 찾아가는 횟수가 판매 줄 수에 비례하면 일량은 판매 줄 수로 정해진다. CF05는 시작일(APL_DT)만 있는 점이력이다. LEAD(APL_DT, 1, '99991231') OVER (PARTITION BY ITM_NO ORDER BY APL_DT)로 같은 상품의 다음 적용일을 종료일로 붙이면, 각 행이 "시작일부터 다음 적용일 전까지"라는 구간이 된다. 그러면 조인 키는 상품 번호 하나(등치)이고, 판매일은 구간 안에 드는지만 보면 되므로 해시 조인을 쓸 수 있다. 판매가 127행으로 구간을 만들어 build로 올리고, 6월 판매상품 45,935줄을 한 번 흘려 맞춘다. CF05는 FULL 한 번(5블록)이고 Buffers는 138,699 → 899로 준다. 경계는 시작일 이상, 다음 적용일 미만으로 붙인다. 종료일 칸에 다음 적용일을 그대로 넣었으므로 <로 비교해야 6월 15일 판매가 옛 구간과 새 구간에 함께 들어가지 않는다. 6월 15일부터는 새 정가가, 6월 14일까지는 옛 정가가 붙는다.

해설

스칼라 서브쿼리로 감싸면: 정가를 스칼라 서브쿼리로 옮기면 입력값(상품, 판매일)이 같은 줄은 캐시로 건너뛴다. 6월 한 달이면 조합이 88 × 30 = 2,640가지라 캐시가 다 담지 못하고, 감사 기간처럼 기간이 길수록 효과가 줄어든다 (교재 07화 §9).
경계 함정: 종료일을 '다음 적용일 - 1일'로 만들어 BETWEEN을 쓰는 방법도 있지만, 문자 날짜에서 하루를 빼려면 날짜로 바꿨다 되돌려야 한다. 다음 적용일을 그대로 쓰고 <로 비교하는 편이 간단하고 틀릴 곳이 적다.
다른 정답: 판매상품과 APL_DT <= 판매일인 가격 행을 조인한 뒤 판매 줄마다 KEEP (DENSE_RANK LAST ORDER BY APL_DT)로 마지막 한 건을 고르는 방법도 한 번에 읽는다. 다만 판매 줄 45,935개로 다시 묶어야 해서, 127행으로 구간을 먼저 만드는 쪽이 가볍다.
판매가 이력이 아직 없는 날의 판매(신상품을 가격 등록 전에 판 경우)는 내부 조인에서 빠진다. 원본도 서브쿼리 결과가 NULL이라 빠지므로 결과는 같지만, 대조 리포트라면 외부 조인으로 '정가 없음'을 드러내는 편이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황