「월 매출 전년 대비」 36줄을 내는 데 TEMP를 31MB 쓴다

모범 답안 SQL ​

-- 먼저 36줄로 묶고, 그 36줄 위에서 LAG 로 전년 같은 달을 가져온다
SELECT ym, ord_cnt, ord_amt, prev_amt,
       ROUND((ord_amt - prev_amt) * 100 / NULLIF(prev_amt, 0), 1) AS yoy_pct
  FROM (SELECT TO_CHAR(ORD_DT, 'YYYYMM') AS ym,
               COUNT(*)                  AS ord_cnt,
               SUM(ORD_AMT)              AS ord_amt,
               LAG(SUM(ORD_AMT), 12) OVER (ORDER BY TO_CHAR(ORD_DT, 'YYYYMM')) AS prev_amt
          FROM T_ORD_BIG
         GROUP BY TO_CHAR(ORD_DT, 'YYYYMM'))
 ORDER BY ym

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

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |     36 |00:00:00.31 |    5826 |          |
|   1 |  VIEW                  |           |      1 |     36 |     36 |00:00:00.31 |    5826 |          |
|   2 |   WINDOW SORT          |           |      1 |     36 |     36 |00:00:00.31 |    5826 | 2048 (0) |
|   3 |    HASH GROUP BY       |           |      1 |     36 |     36 |00:00:00.31 |    5826 | 1129K (0)|
|   4 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |          |
---------------------------------------------------------------------------------------------------------

모범 답안 서술 ​

4번 줄 WINDOW SORT가 주문 100만 행 전부를 달 순서로 줄 세운다. COUNT(*) OVER (PARTITION BY 달)과 SUM(ORD_AMT) OVER (PARTITION BY 달)은 행을 줄이지 않고 100만 행마다 그달의 합계를 붙이기 때문이다. 이 정렬이 작업 공간에 다 들어가지 않아 한 번 TEMP로 내렸다 올렸고(Used-Mem (1), Used-Tmp 31M, Reads와 Writes 3,890), 1.62초의 대부분이 여기서 나온다. 3번 줄 HASH UNIQUE는 그렇게 만든 100만 행에서 똑같은 줄을 지워 36줄을 남긴다. 36줄이 필요한데 100만 행을 정렬하고 99만 9,964행을 버린 셈이다. GROUP BY로 먼저 36줄로 묶으면(HASH GROUP BY, 묶음이 36개라 작업 공간이 1M쯤) 분석 함수는 그 36줄 위에서 돈다. 분석 함수는 GROUP BY가 끝난 뒤에 계산되므로 LAG(SUM(ORD_AMT), 12) OVER (ORDER BY 달)처럼 집계 함수를 그대로 넣을 수 있다. 'GROUP BY와 LAG를 같이 못 쓴다'는 것은 오해다. 고친 SQL은 TEMP를 쓰지 않고 정렬은 36줄 한 번이다. Buffers는 5,826으로 같고 시간은 1.62초 → 0.31초다. LAG(…, 12)는 '12달 전'이 아니라 '12줄 앞'이다. 36개월이 빠짐없이 한 줄씩 있어야 전년 같은 달과 맞는다. 주문이 없는 달이 생기면 그달 줄이 사라져 그 뒤의 비교가 한 달씩 어긋난다. 그럴 수 있는 데이터라면 달력(36개월)에 바깥 조인해 빈 달을 0으로 채운 뒤 LAG를 쓴다.

해설 ​

• 처리 순서: 한 쿼리 블록 안에서는 WHERE, GROUP BY, HAVING, 분석 함수, ORDER BY 순으로 처리된다. 그래서 분석 함수 안에 SUM(ORD_AMT) 같은 집계를 넣을 수 있고, 분석 함수 결과로 거르려면 바깥 쿼리에서 걸러야 한다.
• PGA를 늘리는 처방: 작업 공간을 키우면 4번 줄이 TEMP 없이 끝나 시간은 준다. 하지만 100만 행 정렬은 그대로이고 주문이 늘면 다시 모자란다. 정렬할 행을 36줄로 줄이는 쪽이 먼저다(교재 09화 §6 판정 순서).
• 2022년 열두 달은 전년이 없어 prev_amt가 NULL이고 증감률도 NULL이다. 화면에서는 빈칸으로 둔다. 분모가 0인 달은 NULLIF로 0 나누기를 막는다.
• 이 대시보드는 하루 200번 같은 3년 치를 다시 더한다. 지난달까지의 35줄은 바뀌지 않으므로 월 요약 표를 두면 Buffers까지 줄일 수 있다. 다만 지난 주문의 금액 정정을 반영하는 규칙이 새로 필요하다(JOIN-031).

관련 교재 ​

• 09화 · PGA부터 올리자를 판정하는 법