심야 영업 주문 수가 매장 POS 집계보다 두 배 가까이 크게 나온다

중급SELECT버거57 / 700

상황

버거 본사는 23시부터 다음 날 01시까지의 심야 영업 실적을 매장마다 본다. 「심야 주문」 화면은 영업일(:v_dt)과 그다음 날(:v_dt1)을 받아 두 시간 동안의 주문 수를 매장과 시간대(23시, 0시)별로 센다. 점주들이 "POS의 심야 집계보다 본사 화면 숫자가 두 배 가까이 크다"고 항의했다. 담당 개발자는 "날짜도 시각도 인덱스 컬럼으로 거르는데 뭐가 틀렸는지 모르겠다"고 한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) '230512' · SHOP_NO(매장 200곳) · 주문 일자 순으로 쌓였다 · 하루 평균 1,368건
인덱스 ORD_X01 (ORD_DT, ORD_HMS): CF 22,223 · BLEVEL 2 · 통계 최신
영업일 2013-03-15 무렵: 3월 15일 0시대 15건 · 3월 15일 23시대 27건 · 3월 16일 0시대 14건 · 3월 16일 23시대 25건

원본 SQL

sql

SELECT SHOP_NO, SUBSTR(ORD_HMS, 1, 2) AS hh, COUNT(*) AS night_cnt
  FROM ORD
 WHERE ORD_DT IN (:v_dt, :v_dt1)
   AND (ORD_HMS >= '230000' OR ORD_HMS < '010000')
 GROUP BY SHOP_NO, SUBSTR(ORD_HMS, 1, 2)

실행계획 학습용 예시

xplan

[:v_dt = '20130315' · :v_dt1 = '20130316']
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |     76 | 00:00:00.01 |      26 |
|   1 |  HASH GROUP BY                         |         |      1 |     36 |     76 | 00:00:00.01 |      26 |
|   2 |   INLIST ITERATOR                      |         |      1 |        |     81 | 00:00:00.01 |      26 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | ORD     |      2 |     36 |     81 | 00:00:00.01 |      26 |
|*  4 |     INDEX RANGE SCAN                   | ORD_X01 |      2 |     36 |     81 | 00:00:00.01 |      16 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(("ORD_DT"=:V_DT OR "ORD_DT"=:V_DT1))
       filter(("ORD_HMS">='230000' OR "ORD_HMS"<'010000'))

문제

영업일 하루의 심야 주문은 41건인데 화면이 81건을 세는 이유를 IN 목록과 OR의 조합으로 설명하시오.
4번 줄에서 ORD_HMS 조건이 access가 아닌 이유를 설명하고, 결과가 맞으면서 날짜와 시각 두 컬럼이 모두 access가 되도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT SHOP_NO, SUBSTR(ORD_HMS, 1, 2) AS hh, COUNT(*) AS night_cnt
  FROM ORD
 WHERE (ORD_DT = :v_dt AND ORD_HMS >= '230000')                       -- 영업일 23시대
    OR (ORD_DT = TO_CHAR(TO_DATE(:v_dt, 'YYYYMMDD') + 1, 'YYYYMMDD')
        AND ORD_HMS < '010000')                                        -- 다음 날 0시대
 GROUP BY SHOP_NO, SUBSTR(ORD_HMS, 1, 2)

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

xplan

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Starts | E-Rows | A-Rows | A-Time      | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |      1 |        |     39 | 00:00:00.01 |      12 |
|   1 |  HASH GROUP BY                          |                 |      1 |     40 |     39 | 00:00:00.01 |      12 |
|   2 |   VIEW                                  | VW_ORE_2B7F09C4 |      1 |     40 |     41 | 00:00:00.01 |      12 |
|   3 |    UNION-ALL                            |                 |      1 |        |     41 | 00:00:00.01 |      12 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD             |      1 |     26 |     27 | 00:00:00.01 |       6 |
|*  5 |      INDEX RANGE SCAN                   | ORD_X01         |      1 |     26 |     27 | 00:00:00.01 |       3 |
|*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD             |      1 |     14 |     14 | 00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN                   | ORD_X01         |      1 |     14 |     14 | 00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("ORD_DT"=:V_DT AND "ORD_HMS">='230000')
   6 - filter((LNNVL("ORD_DT"=:V_DT) OR LNNVL("ORD_HMS">='230000')))
   7 - access("ORD_DT"=TO_CHAR(TO_DATE(:V_DT,'YYYYMMDD')+1,'YYYYMMDD') AND "ORD_HMS"<'010000')

모범 답안 서술

IN 목록과 OR는 서로 짝을 맞춰 주지 않는다. ORD_DT IN (:v_dt, :v_dt1) AND (23시 이후 OR 1시 전)은 날짜 둘과 시간대 둘의 네 조합을 모두 받는다. 원한 것은 영업일 23시대(27건)와 다음 날 0시대(14건)뿐인데, 영업일 0시대(15건)는 전날 영업일의 심야이고 다음 날 23시대(25건)는 다음 영업일의 심야다. 둘이 섞여 15 + 27 + 14 + 25 = 81건이 됐다. 원하는 41건의 두 배 가까이다. 4번 줄을 보면 access는 ORD_DT의 두 값뿐이고 ORD_HMS 조건은 filter다. 같은 날짜 안에서 ORD_HMS는 정렬되어 있지만 '23시 이후 OR 1시 전'은 정렬의 양 끝 두 구간이라 한 범위로 정할 수 없다. 그래서 INLIST ITERATOR가 날짜마다 그날 인덱스 항목 1,368개 안팎을 처음부터 끝까지 읽으며 시각을 거른다(4번 줄 Buffers 16). 날짜와 시각 범위를 짝지어 (ORD_DT = :v_dt AND ORD_HMS >= '230000') OR (ORD_DT = 다음 날 AND ORD_HMS < '010000')으로 쓴다. 이제 갈래마다 (날짜 =, 시각 범위)가 인덱스의 한 구간이 되고, 옵티마이저가 OR-Expansion으로 두 갈래를 나눠 갈래마다 두 컬럼을 다 access로 쓴다(5번과 7번 줄). 결과는 41건으로 맞고 Buffers는 26 → 12다. 다음 날은 화면이 따로 넘기지 말고 SQL 안에서 TO_CHAR(TO_DATE(:v_dt, 'YYYYMMDD') + 1, 'YYYYMMDD')로 계산해, 화면이 엉뚱한 날짜 짝을 넘기는 일도 막는다.

해설

함정: 심야를 ORD_DT || ORD_HMS BETWEEN :v_dt || '230000' AND :v_dt1 || '010000'으로 이으면 결과는 맞지만 두 컬럼을 결합해 가공했으므로 인덱스 범위를 정하지 못하고 테이블 전체를 읽는다(IDX-011과 같은 함정).
UNION ALL로 직접 나눠도 된다. 두 갈래는 날짜가 달라 겹치지 않으므로 LNNVL 없이도 결과가 같다. 옵티마이저가 펼칠 때는 겹치지 않는다는 것을 증명하지 않으므로 6번 줄처럼 LNNVL을 붙인다.
IN 목록과 OR를 AND로 묶으면 조합이 곱으로 생긴다. 검색 화면에서 '기간 + 시간대'를 받을 때 자주 틀리는 자리다.
원본도 Buffers는 26으로 작았다. 이 문제의 첫 번째 결함은 속도가 아니라 숫자가 틀린 것이다. 고친 뒤에는 POS 집계와 건수를 맞춰 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황