판매 테이블을 한 번만 읽게 고친 1주, 4주, 13주 그래프에서 4주와 13주 막대가 낮아졌다

중급SELECT유통298 / 700

상황

유통 본사 매장 관리 화면의 「최근 판매 추이」 그래프는 매장마다 최근 1주, 4주, 13주 판매 건수를 막대 세 개로 보여 준다. 점장과 본사 담당자가 하루 수백 번 열고, 한 번에 0.3초가 걸린다. 지난주 조민우가 "판매 테이블을 세 번 읽는다"며 한 번만 읽도록 고쳤는데, 시험 화면에서 4주와 13주 막대가 원래보다 낮게 나와 반영을 미뤘다. 아래는 기준일 2023-12-31(1주 20231225 ~, 4주 20231204 ~, 13주 20231002 ~)로 연 화면이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · SHP_NO NUMBER(5) 1001 ~ 1005 · 이 문제에서는 CF10_PK (SLE_NO) 말고는 인덱스가 없다
기준일까지 1주 4,862건 · 4주 19,537건 · 13주 63,688건(매장별로는 그 1/5 안팎) · 다섯 매장 모두 매일 판매가 있다
화면은 세 기간의 시작일과 기준일을 바인드(:v_w1, :v_w4, :v_w13, :v_to)로 넘긴다 · 결과는 15행(3기간 × 5매장)
통계 최신

원본 SQL

sql

-- 원래 SQL (운영 중)
SELECT '01W' AS PRD, SHP_NO, COUNT(*) AS SLE_CNT
  FROM CF10
 WHERE SLE_DT BETWEEN :v_w1 AND :v_to
 GROUP BY SHP_NO
UNION ALL
SELECT '04W', SHP_NO, COUNT(*)
  FROM CF10
 WHERE SLE_DT BETWEEN :v_w4 AND :v_to
 GROUP BY SHP_NO
UNION ALL
SELECT '13W', SHP_NO, COUNT(*)
  FROM CF10
 WHERE SLE_DT BETWEEN :v_w13 AND :v_to
 GROUP BY SHP_NO
 ORDER BY 1, 2;

-- 조민우가 고친 SQL (4주와 13주가 작게 나온다)
SELECT CASE WHEN SLE_DT >= :v_w1 THEN '01W'
            WHEN SLE_DT >= :v_w4 THEN '04W'
            ELSE '13W' END AS PRD,
       SHP_NO, COUNT(*) AS SLE_CNT
  FROM CF10
 WHERE SLE_DT BETWEEN :v_w13 AND :v_to
 GROUP BY CASE WHEN SLE_DT >= :v_w1 THEN '01W'
               WHEN SLE_DT >= :v_w4 THEN '04W'
               ELSE '13W' END, SHP_NO
 ORDER BY 1, 2;

실행계획 학습용 예시

xplan

[원래 SQL]
------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |      A-Time | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |     15 | 00:00:00.29 |   13749 |
|   1 |  SORT ORDER BY         |      |      1 |     15 |     15 | 00:00:00.29 |   13749 |
|   2 |   UNION-ALL            |      |      1 |        |     15 | 00:00:00.29 |   13749 |
|   3 |    HASH GROUP BY       |      |      1 |      5 |      5 | 00:00:00.09 |    4583 |
|*  4 |     TABLE ACCESS FULL  | CF10 |      1 |   4795 |   4862 | 00:00:00.09 |    4583 |
|   5 |    HASH GROUP BY       |      |      1 |      5 |      5 | 00:00:00.10 |    4583 |
|*  6 |     TABLE ACCESS FULL  | CF10 |      1 |  19180 |  19537 | 00:00:00.10 |    4583 |
|   7 |    HASH GROUP BY       |      |      1 |      5 |      5 | 00:00:00.10 |    4583 |
|*  8 |     TABLE ACCESS FULL  | CF10 |      1 |  62330 |  63688 | 00:00:00.10 |    4583 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(("SLE_DT">=:V_W1 AND "SLE_DT"<=:V_TO))
   6 - filter(("SLE_DT">=:V_W4 AND "SLE_DT"<=:V_TO))
   8 - filter(("SLE_DT">=:V_W13 AND "SLE_DT"<=:V_TO))

[조민우의 SQL]  TABLE ACCESS FULL 한 번 · Buffers 4,583
  기간별 합계  01W 4,862 · 04W 14,675 · 13W 44,151   (원래 SQL 4,862 · 19,537 · 63,688)

문제

원래 SQL이 한 번에 읽는 블록 수를 줄 번호로 설명하고, 조민우의 SQL에서 4주와 13주 숫자가 작아진 이유를 설명하시오.
결과 15행이 원래와 같도록 CF10을 한 번만 읽는 SQL을 작성하시오. 행을 복제하는 시점(집계 전인지 뒤인지)에 따라 일량이 어떻게 다른지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 13주를 한 번 읽어 매장별로 묶으면서, 겹치는 세 기간은 행이 아니라 칸으로 센다
-- ② 묶은 5행을 번호 세 개(1, 2, 3)와 카티션 곱으로 복제해 기간 행 15개로 펼친다
SELECT DECODE(c.NO, 1, '01W', 2, '04W', 3, '13W')   AS PRD,
       a.SHP_NO,
       DECODE(c.NO, 1, a.W01, 2, a.W04, 3, a.W13)   AS SLE_CNT
  FROM (SELECT SHP_NO,
               COUNT(CASE WHEN SLE_DT >= :v_w1 THEN 1 END) AS W01,
               COUNT(CASE WHEN SLE_DT >= :v_w4 THEN 1 END) AS W04,
               COUNT(*)                                     AS W13
          FROM CF10
         WHERE SLE_DT BETWEEN :v_w13 AND :v_to
         GROUP BY SHP_NO) a,
       (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 3) c
 ORDER BY 1, 2

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

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Starts | E-Rows | A-Rows |      A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |      1 |        |     15 | 00:00:00.10 |    4583 |
|   1 |  SORT ORDER BY                    |      |      1 |     15 |     15 | 00:00:00.10 |    4583 |
|   2 |   MERGE JOIN CARTESIAN            |      |      1 |     15 |     15 | 00:00:00.10 |    4583 |
|   3 |    VIEW                           |      |      1 |      1 |      3 | 00:00:00.01 |       0 |
|   4 |     CONNECT BY WITHOUT FILTERING  |      |      1 |        |      3 | 00:00:00.01 |       0 |
|   5 |      FAST DUAL                    |      |      1 |      1 |      1 | 00:00:00.01 |       0 |
|   6 |    BUFFER SORT                    |      |      3 |      5 |     15 | 00:00:00.10 |    4583 |
|   7 |     VIEW                          |      |      1 |      5 |      5 | 00:00:00.10 |    4583 |
|   8 |      HASH GROUP BY                |      |      1 |      5 |      5 | 00:00:00.10 |    4583 |
|*  9 |       TABLE ACCESS FULL           | CF10 |      1 |  62330 |  63688 | 00:00:00.09 |    4583 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   9 - filter(("SLE_DT">=:V_W13 AND "SLE_DT"<=:V_TO))

모범 답안 서술

4번, 6번, 8번 줄이 기간마다 CF10을 FULL로 읽는다(4,583블록씩). 세 기간은 모두 기준일에서 끝나고 13주 안에 4주가, 4주 안에 1주가 들어 있다. 13주를 한 번 읽으면 필요한 판매가 모두 있는데 세 번 읽어 3 × 4,583 = 13,749블록이다. 조민우의 SQL은 한 번만 읽지만 GROUP BY CASE …로 기간을 나눴다. CASE는 한 행에 값 하나만 돌려주고 처음 맞는 WHEN에서 멈춘다. 그래서 12월 28일 판매는 '01W'로만 세어지고 4주와 13주에서는 빠진다. 기간이 겹치는데 겹치지 않는 구간(마지막 1주, 그 앞 3주, 그 앞 9주)으로 나눈 셈이라 4주는 19,537 → 14,675, 13주는 63,688 → 44,151이 됐다. 고친 SQL은 CF10을 13주 범위로 한 번 읽어 매장별로 묶으면서, 세 기간을 행이 아니라 칸으로 센다. COUNT(CASE WHEN SLE_DT >= :v_w1 THEN 1 END), COUNT(CASE WHEN SLE_DT >= :v_w4 THEN 1 END), COUNT(*)는 한 행을 세 칸에서 각각 따지므로 겹치는 기간도 바르게 센다. 그 결과 5행을 CONNECT BY LEVEL <= 3으로 만든 세 행과 카티션 곱으로 복제하고, DECODE로 복제 번호에 맞는 칸을 골라 기간 행 15개로 펼친다. Buffers는 13,749 → 4,583이고 결과는 원래와 같다. 복제하는 시점이 일량을 가른다. 기간 표(3행)를 판매에 SLE_DT >= 시작일 조건으로 조인해 집계 전에 복제해도 답은 맞다. 그러면 13주 판매 63,688건이 기간마다 한 번씩 늘어 88,087행(4,862 + 19,537 + 63,688)을 만든 뒤 묶는다. 집계 뒤에 복제하면 매장 5행을 세 번씩, 15행만 만든다. 복제는 행이 가장 적을 때 한다.

해설

매장 행이 비는 경우: 원래 SQL은 1주 판매가 0건인 매장의 '01W' 행을 만들지 않는다. 고친 SQL은 그 매장의 '01W'를 0으로 만든다. 지금은 다섯 매장이 매일 판매해서 같지만, 그래프가 0 막대를 그려도 되는지 화면 주인에게 확인한다.
누적 합계로 고치는 안: 겹치지 않는 구간(1주, 3주, 9주)으로 한 번 묶은 뒤 SUM(COUNT(*)) OVER (PARTITION BY SHP_NO ORDER BY 구간)으로 누적해도 겹치는 기간의 건수가 된다. 조민우의 SQL에 누적 한 단계를 더한 모양이다.
복제용 테이블: CONNECT BY LEVEL 대신 1부터 n까지 번호가 든 작은 복제용 테이블(COPY_T)을 두고 쓰는 현장도 많다. 어느 쪽이든 복제할 행 수(여기서 3)를 SQL이 정확히 알아야 한다.
판매일자 인덱스가 있으면 13주 범위만 읽어 더 줄일 수 있다(약 600블록). 갈래 셋이 각각 범위를 읽던 원래 SQL은 인덱스가 있어도 겹치는 범위를 세 번 읽는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황