「품절 의심 구간」이 6초 걸리고, 12월 초부터 안 팔린 상품은 목록에 없다

중급SELECT유통356 / 700

상황

본사 상품팀의 「품절 의심 구간」은 2023년에 상품마다 판매가 7일 넘게 끊긴 구간을 찾는다. 판매가 끊긴 구간은 매장 재고가 바닥났다는 신호여서, 발주 담당이 거래처와 통화할 때 근거로 쓴다. 한 번 여는 데 6초가 걸린다. 발주 담당은 이렇게 말한다. "상품 1083은 12월 4일 뒤로 한 번도 안 팔렸는데 목록에 없어요. 지금 품절인 게 제일 급한데요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8). 2023년 판매는 250,025건이다.
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO). 2023년 판매상품은 550,055줄이고 팔린 상품은 88개다.
상품과 판매일의 쌍(그 상품이 팔린 날)은 2023년에 24,156개다. 많이 팔리는 상품은 거의 매일, 드문 상품은 한 해에 수십 일 팔린다.
이 화면에 쓸 인덱스는 PK뿐이다. 통계는 최신이다.

원본 SQL

sql

SELECT x.ITM_NO, x.SLE_DT AS from_dt, MIN(y.SLE_DT) AS to_dt,
       TO_DATE(MIN(y.SLE_DT), 'YYYYMMDD') - TO_DATE(x.SLE_DT, 'YYYYMMDD') - 1 AS no_sale_days
  FROM (SELECT DISTINCT b.ITM_NO, a.SLE_DT
          FROM CF10 a, CF11 b
         WHERE a.SLE_DT BETWEEN '20230101' AND '20231231'
           AND b.SLE_NO = a.SLE_NO) x,
       (SELECT DISTINCT b.ITM_NO, a.SLE_DT
          FROM CF10 a, CF11 b
         WHERE a.SLE_DT BETWEEN '20230101' AND '20231231'
           AND b.SLE_NO = a.SLE_NO) y
 WHERE y.ITM_NO = x.ITM_NO
   AND y.SLE_DT > x.SLE_DT
 GROUP BY x.ITM_NO, x.SLE_DT
HAVING TO_DATE(MIN(y.SLE_DT), 'YYYYMMDD') - TO_DATE(x.SLE_DT, 'YYYYMMDD') - 1 >= 7
 ORDER BY x.ITM_NO, x.SLE_DT

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |     37 |00:00:06.12 |   25574 |          |
|*  1 |  FILTER                  |      |      1 |        |     37 |00:00:06.12 |   25574 |          |
|   2 |   SORT GROUP BY          |      |      1 |  24156 |  24068 |00:00:06.11 |   25574 | 1517K (0)|
|*  3 |    HASH JOIN             |      |      1 |   3313K|   3296K|00:00:04.87 |   25574 | 1430K (0)|
|   4 |     VIEW                 |      |      1 |  24156 |  24156 |00:00:00.79 |   12787 |          |
|   5 |      HASH UNIQUE         |      |      1 |  24156 |  24156 |00:00:00.79 |   12787 | 2271K (0)|
|*  6 |       HASH JOIN          |      |      1 |    550K|    550K|00:00:00.62 |   12787 |   11M (0)|
|*  7 |        TABLE ACCESS FULL | CF10 |      1 |    250K|    250K|00:00:00.04 |    4583 |          |
|   8 |        TABLE ACCESS FULL | CF11 |      1 |   1096K|   1096K|00:00:00.11 |    8204 |          |
|   9 |     VIEW                 |      |      1 |  24156 |  24156 |00:00:00.81 |   12787 |          |
|  10 |      HASH UNIQUE         |      |      1 |  24156 |  24156 |00:00:00.81 |   12787 | 2271K (0)|
|* 11 |       HASH JOIN          |      |      1 |    550K|    550K|00:00:00.63 |   12787 |   11M (0)|
|* 12 |        TABLE ACCESS FULL | CF10 |      1 |    250K|    250K|00:00:00.04 |    4583 |          |
|  13 |        TABLE ACCESS FULL | CF11 |      1 |   1096K|   1096K|00:00:00.11 |    8204 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(MIN("Y"."SLE_DT"),'YYYYMMDD')-TO_DATE("X"."SLE_DT",'YYYYMMDD')-1>=7)
   3 - access("Y"."ITM_NO"="X"."ITM_NO")
       filter("Y"."SLE_DT">"X"."SLE_DT")
   6 - access("B"."SLE_NO"="A"."SLE_NO")
   7 - filter(("A"."SLE_DT">='20230101' AND "A"."SLE_DT"<='20231231'))
  11 - access("B"."SLE_NO"="A"."SLE_NO")
  12 - filter(("A"."SLE_DT">='20230101' AND "A"."SLE_DT"<='20231231'))

문제

3번 줄 HASH JOIN이 어떤 짝을 몇 개 만드는지 쓰고, 6초가 어디서 나오는지 설명하시오.
판매일 쌍을 한 번만 만들어 상품마다 다음 판매일을 구하도록 SQL을 고치시오. 상품 1083처럼 마지막 판매 뒤로 안 팔린 구간도 나오게 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판매일 쌍은 한 번만 만들고, 상품별로 줄 세워 바로 다음 판매일을 LEAD 로 가져온다
SELECT ITM_NO, SLE_DT AS from_dt, next_dt AS to_dt,
       TO_DATE(next_dt, 'YYYYMMDD') - TO_DATE(SLE_DT, 'YYYYMMDD') - 1 AS no_sale_days
  FROM (SELECT ITM_NO, SLE_DT,
               NVL(LEAD(SLE_DT) OVER (PARTITION BY ITM_NO ORDER BY SLE_DT),
                   '20240101') AS next_dt              -- 마지막 판매 뒤로는 기간 끝(다음 날)까지
          FROM (SELECT DISTINCT b.ITM_NO, a.SLE_DT
                  FROM CF10 a, CF11 b
                 WHERE a.SLE_DT BETWEEN '20230101' AND '20231231'
                   AND b.SLE_NO = a.SLE_NO))
 WHERE TO_DATE(next_dt, 'YYYYMMDD') - TO_DATE(SLE_DT, 'YYYYMMDD') - 1 >= 7
 ORDER BY ITM_NO, SLE_DT

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |     41 |00:00:00.83 |   12787 |          |
|*  1 |  VIEW                  |      |      1 |  24156 |     41 |00:00:00.83 |   12787 |          |
|   2 |   WINDOW SORT          |      |      1 |  24156 |  24156 |00:00:00.83 |   12787 | 1189K (0)|
|   3 |    VIEW                |      |      1 |  24156 |  24156 |00:00:00.80 |   12787 |          |
|   4 |     HASH UNIQUE        |      |      1 |  24156 |  24156 |00:00:00.80 |   12787 | 2271K (0)|
|*  5 |      HASH JOIN         |      |      1 |    550K|    550K|00:00:00.63 |   12787 |   11M (0)|
|*  6 |       TABLE ACCESS FULL| CF10 |      1 |    250K|    250K|00:00:00.04 |    4583 |          |
|   7 |       TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.11 |    8204 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE("NEXT_DT",'YYYYMMDD')-TO_DATE("SLE_DT",'YYYYMMDD')-1>=7)
   5 - access("B"."SLE_NO"="A"."SLE_NO")
   6 - filter(("A"."SLE_DT">='20230101' AND "A"."SLE_DT"<='20231231'))

모범 답안 서술

x와 y는 똑같은 인라인 뷰다. 둘 다 2023년 CF10과 CF11을 조인해(12,787블록씩) 상품과 판매일의 쌍 24,156개를 만든다. 3번 줄 HASH JOIN은 상품이 같은 쌍끼리 맞춘 뒤 y.SLE_DT > x.SLE_DT로 거르므로(filter), 판매일마다 그 상품의 뒤쪽 판매일 전부와 짝을 짓는다. 상품마다 판매일 수의 제곱의 절반쯤인 3,296K쌍이 나오고, 2번 줄이 그것을 다시 묶어 판매일마다 가장 이른 뒤쪽 날(MIN) 하나만 남긴다. 바로 다음 판매일 하나를 찾으려고 330만 쌍을 만들고 버린 것이 6초의 대부분이다. LEAD(SLE_DT) OVER (PARTITION BY ITM_NO ORDER BY SLE_DT)는 상품별로 판매일을 줄 세운 뒤 바로 다음 줄의 판매일을 가져온다. 판매일 쌍은 한 번만 만들고(12,787블록) 정렬도 24,156줄을 한 번 한다. 바깥 ORDER BY가 분석 함수의 정렬 순서와 같아 정렬이 더 붙지 않는다. 원래 SQL은 내부 조인이라 상품의 마지막 판매일에는 뒤쪽 짝이 없어 결과에서 빠진다. 그래서 마지막 판매 뒤로 지금까지 이어지는 구간, 곧 가장 급한 품절이 보이지 않았다. LEAD는 마지막 줄에 NULL을 돌려주므로 NVL(…, '20240101')로 기간 다음 날을 넣어 그 구간도 센다. 상품 1083은 12월 4일 뒤로 27일 동안 판매가 없는 구간으로 나온다. 결과는 37줄 → 41줄(진행 중인 구간 4개가 더해진다), Buffers는 25,574 → 12,787, 시간은 6.12초 → 0.83초다.

해설

기간 앞쪽 끝도 같은 문제다: 1월 1일부터 첫 판매일까지 끊긴 구간은 원래 SQL도 고친 SQL도 세지 않는다. 필요하면 LAG(SLE_DT, 1, '20221231')로 앞 판매일을 구해 같은 방법으로 센다.
한 해 동안 한 번도 안 팔린 상품(109개 가운데 21개)은 판매일 쌍이 없어 어느 쪽에도 나오지 않는다. 단종인지 품절인지는 상품 마스터(CF01.SLE_YN)와 함께 본다.
WITH 절로 한 번만 쓰면 되지 않나: 두 번 참조하는 WITH 절은 임시 테이블로 한 번만 만들어질 수 있다(MATERIALIZE). 그래도 330만 쌍을 만드는 자기 조인은 그대로다. 비용의 대부분은 조인의 모양에서 나온다.
날짜가 VARCHAR2라서 날짜 차이는 TO_DATE로 바꿔 계산한다. 문자열끼리 빼면('20231201' - '20231130') 숫자로 바뀌어 달이 바뀌는 구간에서 틀린다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황