「매장 일 실적 보드」를 한 번 열면 주문 인덱스를 1만 8천 번 찾고, 쉰 날은 보드에서 빠진다

실전SELECT버거362 / 700

상황

본사 영업관리팀의 「매장 일 실적 보드」는 고른 달의 200개 매장 × 날짜 칸마다 주문 수, 전일 대비 증감, 월 누적 주문 수, 최근 7일 평균을 보여 준다. 지역 담당 20명과 영업관리팀이 하루 약 300번 연다. 한 번 여는 데 1.2초가 걸리고 DB CPU 상위 SQL 2위다. SH0042 점장이 이렇게 말한다. "리모델링으로 9월 9일부터 11일까지 쉬었는데 보드에 그 사흘이 아예 없어요. 쉰 날도 0으로 보여 주세요." 개발팀이 낸 안은 셋이다. ㉠ 지금 SQL을 두고 인덱스를 더 만든다. ㉡ 분석 함수로 한 번에 계산한다. ㉢ 매장 일 집계 표를 따로 만든다. 박팀장은 "셋을 숫자와 대가로 비교해서 하나를 골라 달라"고 했다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · SHOP_NO 매장 200개(매장마다 하루 약 7건) · 주문일 순으로 쌓여 있다.
인덱스: ORD_PK (ORD_NO), ORD_X01 (ORD_DT, ORD_HMS), IX_ORD_SHOP_DT (SHOP_NO, ORD_DT)(지난달 점장 앱 달력 때문에 만들었다. SORT-044). IX_ORD_SHOP_DT에서 한 매장의 한 달(약 210항목)은 리프 한두 블록에 든다.
SHOP 매장: 200행 · 5블록. 2013년 9월 주문은 41,100건이고, 주문이 한 건도 없는 매장 × 날짜 칸이 10개 있다(SH0042의 9일 ~ 11일 포함). 그래서 보드의 칸은 6,000개인데 SQL은 5,990줄을 낸다.
통계는 최신이다.

원본 SQL

sql

SELECT d.SHOP_NO, d.ORD_DT, d.ord_cnt,
       d.ord_cnt - (SELECT COUNT(*) FROM ORD p
                     WHERE p.SHOP_NO = d.SHOP_NO
                       AND p.ORD_DT  = TO_CHAR(TO_DATE(d.ORD_DT, 'YYYYMMDD') - 1, 'YYYYMMDD')) AS diff_prev,
       (SELECT COUNT(*) FROM ORD c
         WHERE c.SHOP_NO = d.SHOP_NO
           AND c.ORD_DT BETWEEN SUBSTR(d.ORD_DT, 1, 6) || '01' AND d.ORD_DT)              AS cum_cnt,
       (SELECT ROUND(COUNT(*) / 7, 1) FROM ORD w
         WHERE w.SHOP_NO = d.SHOP_NO
           AND w.ORD_DT BETWEEN TO_CHAR(TO_DATE(d.ORD_DT, 'YYYYMMDD') - 6, 'YYYYMMDD')
                            AND d.ORD_DT)                                                AS avg7
  FROM (SELECT SHOP_NO, ORD_DT, COUNT(*) AS ord_cnt
          FROM ORD
         WHERE ORD_DT BETWEEN :ym || '01' AND :ym || '31'
         GROUP BY SHOP_NO, ORD_DT) d
 ORDER BY d.SHOP_NO, d.ORD_DT

실행계획 학습용 예시

xplan

[2013년 9월]
---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |      1 |        |   5990 |00:00:01.21 |   57522 |
|   1 |  SORT AGGREGATE       |                |   5990 |      1 |   5990 |00:00:00.21 |   17970 |
|*  2 |   INDEX RANGE SCAN    | IX_ORD_SHOP_DT |   5990 |      7 |  41034 |00:00:00.17 |   17970 |
|   3 |  SORT AGGREGATE       |                |   5990 |      1 |   5990 |00:00:00.38 |   19770 |
|*  4 |   INDEX RANGE SCAN    | IX_ORD_SHOP_DT |   5990 |    110 |    636K|00:00:00.31 |   19770 |
|   5 |  SORT AGGREGATE       |                |   5990 |      1 |   5990 |00:00:00.26 |   19170 |
|*  6 |   INDEX RANGE SCAN    | IX_ORD_SHOP_DT |   5990 |     48 |    287K|00:00:00.21 |   19170 |
|   7 |  SORT GROUP BY NOSORT |                |      1 |   6000 |   5990 |00:00:00.02 |     612 |
|*  8 |   INDEX SKIP SCAN     | IX_ORD_SHOP_DT |      1 |  41100 |  41100 |00:00:00.01 |     612 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."SHOP_NO"=:B1 AND "P"."ORD_DT"=TO_CHAR(TO_DATE(:B2,'YYYYMMDD')-1,'YYYYMMDD'))
   4 - access("C"."SHOP_NO"=:B1 AND "C"."ORD_DT">=SUBSTR(:B2,1,6)||'01' AND "C"."ORD_DT"<=:B3)
   6 - access("W"."SHOP_NO"=:B1 AND "W"."ORD_DT">=TO_CHAR(TO_DATE(:B2,'YYYYMMDD')-6,'YYYYMMDD')
              AND "W"."ORD_DT"<=:B3)
   8 - access("ORD_DT">=:YM||'01' AND "ORD_DT"<=:YM||'31')
       filter(("ORD_DT">=:YM||'01' AND "ORD_DT"<=:YM||'31'))

문제

한 번 여는 데 57,522블록을 읽고 1.2초가 걸리는 까닭을 Starts로 설명하고, 쉰 날이 보드에서 빠지는 까닭을 쓰시오.
㉠, ㉡, ㉢을 일량과 대가로 비교해 하나를 고르시오.
고른 안으로 SQL을 작성하시오. 달의 첫 며칠과 쉰 날 앞뒤의 값이 맞게 나오도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ㉡ 달 + 앞 6일을 한 번 읽어 매장 × 날짜로 묶고, 쉰 날은 달력으로 채운 뒤 분석 함수로 한 번에 계산한다
SELECT SHOP_NO, ORD_DT, ord_cnt, diff_prev, cum_cnt, avg7
  FROM (SELECT g.SHOP_NO, g.ORD_DT, g.ord_cnt,
               g.ord_cnt - LAG(g.ord_cnt) OVER (PARTITION BY g.SHOP_NO ORDER BY g.ORD_DT)  AS diff_prev,
               SUM(CASE WHEN g.ORD_DT >= :ym || '01' THEN g.ord_cnt END)                    -- 앞 6일은 누적에서 뺀다
                 OVER (PARTITION BY g.SHOP_NO ORDER BY g.ORD_DT ROWS UNBOUNDED PRECEDING)   AS cum_cnt,
               ROUND(AVG(g.ord_cnt) OVER (PARTITION BY g.SHOP_NO ORDER BY g.ORD_DT
                                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1)     AS avg7
          FROM (SELECT s.SHOP_NO, c.dt AS ORD_DT, NVL(o.cnt, 0) AS ord_cnt
                  FROM SHOP s
                       CROSS JOIN
                       (SELECT TO_CHAR(TO_DATE(:ym || '01', 'YYYYMMDD') - 7 + LEVEL, 'YYYYMMDD') AS dt
                          FROM DUAL
                       CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(:ym || '01', 'YYYYMMDD')), 'DD')) + 6) c
                       LEFT OUTER JOIN
                       (SELECT SHOP_NO, ORD_DT, COUNT(*) AS cnt
                          FROM ORD
                         WHERE ORD_DT BETWEEN TO_CHAR(TO_DATE(:ym || '01', 'YYYYMMDD') - 6, 'YYYYMMDD')
                                          AND :ym || '31'
                         GROUP BY SHOP_NO, ORD_DT) o
                       ON (o.SHOP_NO = s.SHOP_NO AND o.ORD_DT = c.dt)) g)
 WHERE ORD_DT >= :ym || '01'                 -- 앞 6일은 계산에만 쓰고, 분석 함수 다음에 버린다
 ORDER BY SHOP_NO, ORD_DT

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

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |      1 |        |   6000 |00:00:00.09 |     645 |          |
|*  1 |  VIEW                              |                |      1 |   7200 |   6000 |00:00:00.09 |     645 |          |
|   2 |   WINDOW SORT                      |                |      1 |   7200 |   7200 |00:00:00.08 |     645 |  432K (0)|
|*  3 |    HASH JOIN RIGHT OUTER           |                |      1 |   7200 |   7200 |00:00:00.06 |     645 | 1580K (0)|
|   4 |     VIEW                           |                |      1 |   7190 |   7188 |00:00:00.04 |     640 |          |
|   5 |      SORT GROUP BY NOSORT          |                |      1 |   7190 |   7188 |00:00:00.04 |     640 |          |
|*  6 |       INDEX SKIP SCAN              | IX_ORD_SHOP_DT |      1 |  49320 |  49320 |00:00:00.02 |     640 |          |
|   7 |     MERGE JOIN CARTESIAN           |                |      1 |   7200 |   7200 |00:00:00.01 |       5 |          |
|   8 |      VIEW                          |                |      1 |      1 |     36 |00:00:00.01 |       0 |          |
|   9 |       CONNECT BY WITHOUT FILTERING |                |      1 |        |     36 |00:00:00.01 |       0 |          |
|  10 |        FAST DUAL                   |                |      1 |      1 |      1 |00:00:00.01 |       0 |          |
|  11 |      BUFFER SORT                   |                |     36 |    200 |   7200 |00:00:00.01 |       5 | 9216 (0) |
|  12 |       TABLE ACCESS FULL            | SHOP           |      1 |    200 |    200 |00:00:00.01 |       5 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ORD_DT">=:YM||'01')
   3 - access("O"."SHOP_NO"="S"."SHOP_NO" AND "O"."ORD_DT"="C"."DT")
   6 - access("ORD_DT">=TO_CHAR(TO_DATE(:YM||'01','YYYYMMDD')-6,'YYYYMMDD') AND "ORD_DT"<=:YM||'31')
       filter(("ORD_DT">=TO_CHAR(TO_DATE(:YM||'01','YYYYMMDD')-6,'YYYYMMDD') AND "ORD_DT"<=:YM||'31'))

[SH0042 · 9월 8일 ~ 12일]  8일 7건 · 9일 0 · 10일 0 · 11일 0 · 12일 8건(전일 대비 +8, 7일 평균 4.1)

모범 답안 서술

보드 한 번에 스칼라 서브쿼리 셋이 5,990번씩, 모두 17,970번 돈다(1, 3, 5번 줄의 Starts). 한 번은 IX_ORD_SHOP_DT의 한두 리프 블록으로 끝나지만(3블록 남짓) 횟수가 많아 57,522블록이 되고, 1.2초의 대부분은 서브쿼리를 1만 8천 번 시작하고 끝내는 CPU다. 월 누적 서브쿼리(3번 줄)는 날마다 그달 1일부터 그날까지를 다시 세므로 같은 주문을 평균 15번쯤 거듭 센다. 본문은 주문이 있는 매장 × 날짜만 묶으므로(GROUP BY), 주문이 0건인 칸 10개(SH0042의 9일 ~ 11일 포함)는 보드에서 빠진다. ㉡을 고른다. 달의 앞 6일을 포함한 36일을 한 번 읽어(INDEX SKIP SCAN, 640블록) 매장 × 날짜로 묶고, 매장 200개 × 날짜 36개의 달력에 바깥 조인해 쉰 날을 0으로 채운다. 그 위에서 전일 대비는 LAG, 월 누적은 SUM … ROWS UNBOUNDED PRECEDING, 최근 7일 평균은 AVG … ROWS BETWEEN 6 PRECEDING AND CURRENT ROW로 구한다. 세 함수가 모두 (매장, 날짜) 한 순서라 정렬은 7,200줄 한 번이고, 바깥 ORDER BY도 같은 순서라 정렬이 더 붙지 않는다. Buffers는 57,522 → 645, 시간은 1.21초 → 0.09초다. 함정이 둘 있다. 첫째, 달력으로 쉰 날을 채우지 않고 LAG와 ROWS를 쓰면 '앞 줄'이 '전날'이 아니게 된다. SH0042의 12일은 8일과 비교되고, 7일 평균은 쉰 사흘을 건너뛴 열흘 치 7줄로 계산된다. 둘째, 1일의 전일 대비와 1 ~ 6일의 7일 평균에는 지난달 마지막 6일이 필요하다. 그래서 앞 6일을 함께 읽어 계산에만 쓰고, 이달 조건은 분석 함수 다음 단계(바깥 쿼리)에서 건다. 같은 블록의 WHERE에 걸면 분석 함수보다 먼저 걸러져 1일이 앞날을 잃는다. 월 누적은 앞 6일을 CASE로 빼고 더한다. ㉠ 지금 SQL을 두고 인덱스를 더 만들어도 서브쿼리 한 번이 이미 3블록 남짓이라 줄일 데가 거의 없다. 일량은 횟수(17,970번)에서 나오므로 인덱스로는 풀리지 않고, ORD에 인덱스가 하나 늘면 주문을 넣을 때마다 고칠 항목만 는다. ㉢ 매장 일 집계 표(매장, 날짜, 주문 수)를 밤마다 만들면 보드는 7,200줄만 읽는다. 하지만 오늘 주문이 빠지고(어제까지의 숫자), 늦게 올라온 주문과 취소를 다시 맞추는 배치가 새로 생긴다(DML-029). ㉡이 645블록이면 하루 300번에 약 19만 블록이라 표를 따로 둘 까닭이 아직 없다. 보드를 여는 횟수가 열 배로 늘면 그때 ㉢을 다시 잰다.

해설

파티션 아우터 조인: 매장 마스터 없이 주문 쪽 매장만으로 채우려면 PARTITION BY (o.SHOP_NO) RIGHT OUTER JOIN 달력을 쓴다. 다만 그달 주문이 한 건도 없는 매장은 줄 자체가 없으므로, 보드에 200개 매장이 모두 나와야 하면 SHOP과 달력의 곱에서 출발한다.
INDEX SKIP SCAN: IX_ORD_SHOP_DT의 선두 컬럼 SHOP_NO에 조건이 없지만 매장이 200개뿐이라 매장마다 날짜 범위로 건너뛰며 읽는다. 필요한 두 컬럼이 인덱스에 다 있어 테이블을 읽지 않는다. 선두 컬럼 값의 종류가 많아지면 이 방식은 비싸진다.
7일 평균의 분모는 '7일'이다. 쉰 날을 0으로 채웠으므로 7줄 평균이 곧 7일 평균이다. 쉰 날을 평균에서 빼야 한다면(영업일 평균) 업무가 정할 일이고, 그때는 쉰 날을 NULL로 두면 AVG가 건너뛴다.
매장 하나만 보는 화면이라면 SHOP과 주문 집계 쪽에 매장 조건을 넣는다. 매장은 PARTITION BY 컬럼이라 분석 함수 앞에서 걸러도 결과가 같다. 반대로 날짜 조건은 분석 함수 앞에서 걸면 결과가 바뀐다.
바꾸는 날에는 9월 1일의 전일 대비(8월 31일 주문 수와 비교)와 SH0042의 12일 값을 원래 SQL과 손으로 대조한다. 쉰 날 칸 10개가 0으로 새로 나오는 것 말고는 원래 결과와 같아야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황