점주 앱의 「주간 주문 추이」가 매장 한 곳의 일주일을 보는데 주문 2년 치를 읽는다

중급SELECT버거287 / 700

상황

가맹점주 앱의 「주간 주문 추이」는 매장 한 곳의 일주일치 일별 주문 수와 전일 대비 증감을 보여 준다. 점주 200명이 아침마다 열어서 출근 시간에 0.3초짜리 호출이 몰린다. 화면 SQL은 공통 뷰 V_SHOP_DAY에 매장과 기간 조건을 건다. 조민우는 "매장 조건은 뷰 안으로 들어가는데 날짜 조건은 왜 안 들어가는지 모르겠다. 날짜 조건을 뷰 안에 직접 넣어 보니 빨라지긴 했는데 첫날 증감이 비어 나온다"라고 한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 2012 ~ 2013년 730일 · 날짜순 적재 · 하루치 약 1,370건이 31블록에 모여 있다
인덱스 ORD_X01 (ORD_DT, ORD_HMS) · 하루치 리프 약 5블록 · SHOP_NO에는 인덱스가 없다
매장 200곳 · 모든 매장이 2년 동안 하루도 빠짐없이 주문을 받았다(한 매장 하루 약 7건) · 통계 최신
아래 실행계획은 :shop = 'SH0042', :d1 = '20130311', :d2 = '20130317'

원본 SQL

sql

-- 공통 뷰: 매장 일별 주문 수와 전일 주문 수
CREATE OR REPLACE VIEW V_SHOP_DAY AS
SELECT SHOP_NO, ORD_DT, COUNT(*) AS ORD_CNT,
       LAG(COUNT(*)) OVER (PARTITION BY SHOP_NO ORDER BY ORD_DT) AS PREV_CNT
  FROM ORD
 GROUP BY SHOP_NO, ORD_DT;

-- 주간 주문 추이
SELECT ORD_DT, ORD_CNT, ORD_CNT - PREV_CNT AS DIFF
  FROM V_SHOP_DAY
 WHERE SHOP_NO = :shop
   AND ORD_DT BETWEEN :d1 AND :d2
 ORDER BY ORD_DT;

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |      7 |00:00:00.28 |   22422 |          |
|   1 |  SORT ORDER BY         |            |      1 |      7 |      7 |00:00:00.28 |   22422 | 2048  (0)|
|*  2 |   VIEW                 | V_SHOP_DAY |      1 |    730 |      7 |00:00:00.28 |   22422 |          |
|   3 |    WINDOW BUFFER       |            |      1 |    730 |    730 |00:00:00.28 |   22422 |36864  (0)|
|   4 |     SORT GROUP BY      |            |      1 |    730 |    730 |00:00:00.28 |   22422 |49152  (0)|
|*  5 |      TABLE ACCESS FULL | ORD        |      1 |   5000 |   5003 |00:00:00.27 |   22422 |          |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_DT">=:D1 AND "ORD_DT"<=:D2))
   5 - filter("SHOP_NO"=:SHOP)

문제

매장 조건은 5번 줄까지 들어갔는데 날짜 조건은 2번 줄 VIEW에 남은 이유를 뷰 정의의 LAG와 관련지어 설명하시오. 조민우가 날짜 조건을 뷰 안에 그대로 넣었을 때 첫날 증감이 빈 이유도 설명하시오.
결과(첫날의 전일 대비 증감 포함)가 바뀌지 않게, 주문을 그 주 언저리만 인덱스로 읽도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 뷰 안(LAG 아래)에는 하루 더 넓혀 읽고, 바깥에서 원래 기간으로 자른다
SELECT ORD_DT, ORD_CNT, ORD_CNT - PREV_CNT AS DIFF
  FROM (SELECT SHOP_NO, ORD_DT, COUNT(*) AS ORD_CNT,
               LAG(COUNT(*)) OVER (PARTITION BY SHOP_NO ORDER BY ORD_DT) AS PREV_CNT
          FROM ORD
         WHERE SHOP_NO = :shop
           AND ORD_DT BETWEEN TO_CHAR(TO_DATE(:d1, 'YYYYMMDD') - 1, 'YYYYMMDD') AND :d2
         GROUP BY SHOP_NO, ORD_DT)
 WHERE ORD_DT >= :d1
 ORDER BY ORD_DT;

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

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |      1 |        |      7 |00:00:00.01 |     287 |          |
|   1 |  SORT ORDER BY                          |         |      1 |      8 |      7 |00:00:00.01 |     287 | 2048  (0)|
|*  2 |   VIEW                                  |         |      1 |      8 |      7 |00:00:00.01 |     287 |          |
|   3 |    WINDOW BUFFER                        |         |      1 |      8 |      8 |00:00:00.01 |     287 | 2048  (0)|
|   4 |     SORT GROUP BY                       |         |      1 |      8 |      8 |00:00:00.01 |     287 | 2048  (0)|
|*  5 |      FILTER                             |         |      1 |        |     55 |00:00:00.01 |     287 |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| ORD    |      1 |     55 |     55 |00:00:00.01 |     287 |          |
|*  7 |        INDEX RANGE SCAN                 | ORD_X01 |      1 |  10960 |  10958 |00:00:00.01 |      39 |          |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_DT">=:D1)
   5 - filter(TO_CHAR(TO_DATE(:D1,'YYYYMMDD')-1,'YYYYMMDD')<=:D2)
   6 - filter("SHOP_NO"=:SHOP)
   7 - access("ORD_DT">=TO_CHAR(TO_DATE(:D1,'YYYYMMDD')-1,'YYYYMMDD') AND "ORD_DT"<=:D2)

모범 답안 서술

뷰에는 LAG(...) OVER (PARTITION BY SHOP_NO ORDER BY ORD_DT)가 있다. 매장 조건은 PARTITION BY 컬럼에 건 조건이라, 다른 매장의 행을 미리 걸러도 남은 행의 LAG 값은 바뀌지 않는다. 그래서 옵티마이저가 매장 조건을 뷰 안으로 밀어 넣어 5번 줄의 테이블 filter가 되었다(조건절 Pushing). 날짜 조건은 다르다. 집계 전에 날짜로 거르면 3월 11일 행의 '앞 행'인 3월 10일이 사라져 LAG 값이 바뀐다. 결과가 달라질 수 있는 조건이므로 옵티마이저는 날짜 조건을 밀어 넣지 않고, 매장의 2년 치 주문 5,003건을 730일로 묶고 LAG까지 계산한 뒤 2번 줄 VIEW에서 7일만 남긴다. SHOP_NO에 인덱스가 없어 그 5,003건을 찾는 데 테이블 전체(22,422블록)를 읽었다. 조민우처럼 ORD_DT BETWEEN :d1 AND :d2를 뷰 안에 그대로 넣으면 바로 그 일이 벌어진다. 3월 10일 주문을 읽지 않았으니 첫날의 PREV_CNT가 NULL이 되고 증감 칸이 빈다. 빨라졌지만 결과가 바뀌었다. 집계 전 조건은 시작일 하루 전부터 주고(TO_DATE(:d1) - 1), 바깥에서 ORD_DT >= :d1로 원래 기간을 다시 자른다. 모든 매장이 매일 주문을 받았으므로 하루만 더 읽으면 첫날의 앞 행이 정확히 전날이다. 이제 ORD_X01로 8일치 10,958건만 읽고(39 + 248블록) 매장 55건을 묶는다. Buffers는 22,422 → 287로 준다.

해설

분석 함수가 있는 뷰의 규칙: 바깥 조건은 PARTITION BY 컬럼에 걸었을 때만 뷰 안으로 들어간다. 그 밖의 조건은 분석 함수가 보는 행의 범위를 바꾸므로 뷰 밖에 남는다. 계획에서 filter가 VIEW 줄에 붙어 있으면 이것부터 의심한다.
함정: 매장이 쉬는 날이 있으면 '하루 전'의 행이 없어 직전 영업일과 비교하던 원래 결과와 달라진다. 그런 데이터라면 직전 영업일을 따로 구해 범위 시작으로 쓰거나 여유를 넉넉히 둔다. 고친 뒤에는 같은 기간을 원래 SQL과 MINUS로 대조한다.
공통 뷰는 그대로 둔다: 저장된 뷰에는 바인드를 넣을 수 없으므로 화면 SQL을 인라인 뷰로 바꾼다. 다른 화면이 전 기간 추이를 보려고 이 뷰를 쓰고 있다면 그쪽은 손댈 필요가 없다.
더 줄이려면: (SHOP_NO, ORD_DT) 인덱스면 매장의 8일치 약 55건만 읽는다(약 60블록). 점주 200명이 매일 여는 화면이라 검토할 만하지만, 100만 행 인덱스 하나가 주문 적재마다 고쳐진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황