「재주문 간격 분포」 다섯 줄을 내는 데 2초 가까이 걸린다

기초SELECT한빛유통355 / 700

상황

마케팅팀의 「재주문 간격 분포」는 2024년 주문마다 같은 고객의 직전 주문과 며칠 떨어졌는지를 구해 다섯 구간(0일, 1일, 2 ~ 3일, 4 ~ 7일, 8일 이상)으로 센다. 매주 월요일 회의 자료에 붙이고, 담당자가 구간을 바꿔 가며 하루 수십 번 돌린다. 다섯 줄짜리 결과인데 2초 가까이 걸린다. 개발자는 "고객마다 주문에 번호를 매기고, n번 주문과 n+1번 주문을 짝지었다"라고 설명한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT는 전부 자정 · ORD_SEQ는 유일하다. 2024년 주문은 333,245건이고 고객 2,000명 모두 2024년 주문이 있다(고객당 약 167건).
같은 고객이 같은 날 두 번 이상 주문하는 일이 흔하다. 1년 치(33%)를 읽으므로 인덱스보다 FULL이 싸다.
통계는 최신이다.

원본 SQL

sql

SELECT CASE WHEN gap = 0  THEN '0일'
            WHEN gap = 1  THEN '1일'
            WHEN gap <= 3 THEN '2~3일'
            WHEN gap <= 7 THEN '4~7일'
            ELSE '8일 이상' END AS gap_band,
       COUNT(*) AS ord_cnt
  FROM (SELECT b.ORD_DT - a.ORD_DT AS gap
          FROM (SELECT CUS_ID, ORD_DT,
                       ROW_NUMBER() OVER (PARTITION BY CUS_ID ORDER BY ORD_DT) AS rn
                  FROM T_ORD_BIG
                 WHERE ORD_DT >= DATE '2024-01-01'
                   AND ORD_DT <  DATE '2025-01-01') a,
               (SELECT CUS_ID, ORD_DT,
                       ROW_NUMBER() OVER (PARTITION BY CUS_ID ORDER BY ORD_DT) AS rn
                  FROM T_ORD_BIG
                 WHERE ORD_DT >= DATE '2024-01-01'
                   AND ORD_DT <  DATE '2025-01-01') b
         WHERE b.CUS_ID = a.CUS_ID
           AND b.rn     = a.rn + 1)
 GROUP BY CASE WHEN gap = 0  THEN '0일'
               WHEN gap = 1  THEN '1일'
               WHEN gap <= 3 THEN '2~3일'
               WHEN gap <= 7 THEN '4~7일'
               ELSE '8일 이상' END

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |      5 |00:00:01.93 |   11652 |          |
|   1 |  HASH GROUP BY         |           |      1 |      5 |      5 |00:00:01.93 |   11652 | 1035K (0)|
|*  2 |   HASH JOIN            |           |      1 |    333K|    331K|00:00:01.81 |   11652 |   12M (0)|
|   3 |    VIEW                |           |      1 |    333K|    333K|00:00:00.52 |    5826 |          |
|   4 |     WINDOW SORT        |           |      1 |    333K|    333K|00:00:00.47 |    5826 |   10M (0)|
|*  5 |      TABLE ACCESS FULL | T_ORD_BIG |      1 |    333K|    333K|00:00:00.09 |    5826 |          |
|   6 |    VIEW                |           |      1 |    333K|    333K|00:00:00.55 |    5826 |          |
|   7 |     WINDOW SORT        |           |      1 |    333K|    333K|00:00:00.49 |    5826 |   10M (0)|
|*  8 |      TABLE ACCESS FULL | T_ORD_BIG |      1 |    333K|    333K|00:00:00.09 |    5826 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."CUS_ID"="A"."CUS_ID" AND "B"."RN"="A"."RN"+1)
   5 - filter(("ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   8 - filter(("ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

문제

실행계획에서 같은 일을 두 번 하는 곳을 찾고, 2번 줄 HASH JOIN이 무엇과 무엇을 맞추는지 설명하시오.
2024년 주문을 한 번만 읽고 한 번만 정렬하도록 SQL을 고치시오. 같은 날 주문이 여럿일 때 순서를 어떻게 정할지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번 읽고, 고객별로 한 번 줄 세운 뒤 바로 앞 주문의 날짜를 LAG 로 가져온다
SELECT CASE WHEN gap = 0  THEN '0일'
            WHEN gap = 1  THEN '1일'
            WHEN gap <= 3 THEN '2~3일'
            WHEN gap <= 7 THEN '4~7일'
            ELSE '8일 이상' END AS gap_band,
       COUNT(*) AS ord_cnt
  FROM (SELECT ORD_DT - LAG(ORD_DT) OVER (PARTITION BY CUS_ID
                                          ORDER BY ORD_DT, ORD_SEQ) AS gap
          FROM T_ORD_BIG
         WHERE ORD_DT >= DATE '2024-01-01'
           AND ORD_DT <  DATE '2025-01-01')
 WHERE gap IS NOT NULL                       -- 고객의 첫 주문(앞 주문 없음)은 원래도 짝이 없었다
 GROUP BY CASE WHEN gap = 0  THEN '0일'
               WHEN gap = 1  THEN '1일'
               WHEN gap <= 3 THEN '2~3일'
               WHEN gap <= 7 THEN '4~7일'
               ELSE '8일 이상' END

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |      5 |00:00:00.61 |    5826 |          |
|   1 |  HASH GROUP BY         |           |      1 |      5 |      5 |00:00:00.61 |    5826 | 1035K (0)|
|*  2 |   VIEW                 |           |      1 |    333K|    331K|00:00:00.56 |    5826 |          |
|   3 |    WINDOW SORT         |           |      1 |    333K|    333K|00:00:00.49 |    5826 |   12M (0)|
|*  4 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |    333K|    333K|00:00:00.09 |    5826 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("GAP" IS NOT NULL)
   4 - filter(("ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

모범 답안 서술

4번과 7번 줄이 같은 일을 두 번 한다. 두 인라인 뷰가 똑같이 T_ORD_BIG 전체(5,826블록)를 읽어 2024년 주문 333,245건을 고르고, 똑같이 고객별 날짜순으로 줄 세워(WINDOW SORT) 번호를 매긴다. 2번 줄 HASH JOIN은 한쪽의 (고객, n번)과 다른 쪽의 (고객, n+1번)을 맞추는 자기 조인이다. 333K행을 메모리에 올려(12M) 333K행을 대어 보고, 고객마다 첫 주문은 짝이 없어 331,245쌍이 나온다. 바로 앞 줄 하나를 보려고 같은 집합을 두 번 만들어 맞춘 셈이다. LAG(ORD_DT) OVER (PARTITION BY CUS_ID ORDER BY ORD_DT, ORD_SEQ)는 고객별로 줄 세운 집합 안에서 바로 앞 줄의 값을 가져온다. 한 번 읽고 한 번 정렬하면 되고 조인이 없다. 고객의 첫 주문은 앞 줄이 없어 NULL이 나오므로 gap IS NOT NULL로 빼야 원래 결과(331,245건)와 같다. 같은 날 주문이 여럿이면 ORD_DT만으로는 순서가 정해지지 않으므로 ORD_SEQ를 붙여 정렬 키를 유일하게 한다. 날짜 차이만 보는 이 집계는 순서가 흔들려도 결과가 같지만, 앞 주문의 금액이나 번호를 함께 가져오는 순간 실행마다 값이 달라질 수 있다. Buffers는 11,652 → 5,826, 시간은 1.93초 → 0.61초다.

해설

2023년 12월의 주문을 직전 주문으로 볼 것인가: 지금은 원래 SQL처럼 2024년 안에서만 짝을 짓는다. 해가 바뀐 뒤의 첫 주문도 세려면 2023년 12월부터 읽어 LAG를 구한 뒤 바깥 쿼리에서 2024년 주문만 남긴다. 기간 조건을 LAG와 같은 블록에 두면 2024년 첫 주문이 짝을 잃는다.
LEAD로 써도 같다: n번 주문을 기준으로 다음 주문과의 간격을 세면 구간별 건수는 같다. 어느 주문에 간격을 붙이느냐만 다르다.
남은 일은 333K행 정렬 한 번(12M)이다. 고객별 날짜순 인덱스 (CUS_ID, ORD_DT, ORD_SEQ)를 처음부터 끝까지 읽으면 정렬이 사라지지만, 테이블을 고객 순서로 33만 번 찾아가야 해서 FULL 한 번보다 비싸다. 1년 치 집계에서는 정렬 한 번이 맞는 값이다.
ROW_NUMBER를 두 번 매기는 자기 조인은 정렬 키가 유일하지 않으면 두 뷰가 동점을 서로 다른 순서로 번호 매길 수 있다. 날짜만 쓰는 이 SQL에서는 티가 나지 않았을 뿐이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황