6월 매출 대사가 장부보다 137건, 3,415만 원 적다

중급SELECT한빛유통13 / 700

상황

정산팀의 「월 매출 대사」 화면은 한 달 주문 건수와 금액을 회계 장부와 맞춰 본다. 지난달까지는 TO_CHAR(ORD_DT, 'YYYYMM') = :v_ym이었는데, 조민우가 '컬럼 가공 금지' 규칙대로 고쳐 배포했다. 빨라졌지만 6월분이 장부(4,110건)와 137건 차이가 난다. 오과장은 상한을 고치는 보정안 두 개를 냈다. 한편 다음 분기 결제 연동 개편으로 주문 시각 컬럼을 TIMESTAMP(3)로 바꾸는 계획이 잡혀 있다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · ORD_DT DATE(자정이 아닌 행 99,999건)
2024년 6월 주문 4,110건 · 그중 6월 30일 주문 137건(34,159,700원)
인덱스 IX_T_ORD_DT_AMT (ORD_DT, ORD_AMT): BLEVEL 1 · 리프 약 300블록 · 통계 최신

원본 SQL

sql

-- [지난달까지]
SELECT COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD
 WHERE TO_CHAR(ORD_DT, 'YYYYMM') = :v_ym;

-- [이번 배포]
SELECT COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD
 WHERE ORD_DT BETWEEN TO_DATE(:v_ym, 'YYYYMM')
                  AND LAST_DAY(TO_DATE(:v_ym, 'YYYYMM'));

-- [오과장 보정안] 이번 배포 판의 상한만 바꾼다
--   (가) ... AND LAST_DAY(TO_DATE(:v_ym, 'YYYYMM')) + 0.99999
--   (나) ... AND TO_DATE(TO_CHAR(LAST_DAY(TO_DATE(:v_ym, 'YYYYMM')), 'YYYYMMDD') || '235959', 'YYYYMMDDHH24MISS')

실행계획 학습용 예시 · 건수는 교재 02화 실측

xplan

[지난달까지 — TO_CHAR]
------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      1 |00:00:00.02 |     896 |
|   1 |  SORT AGGREGATE    |       |      1 |      1 |      1 |00:00:00.02 |     896 |
|*  2 |   TABLE ACCESS FULL| T_ORD |      1 |   1000 |   4110 |00:00:00.02 |     896 |
------------------------------------------------------------------------------------
   2 - filter(TO_CHAR(INTERNAL_FUNCTION("ORD_DT"),'YYYYMM')=:V_YM)

[이번 배포 — BETWEEN … LAST_DAY]
----------------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |      1 |        |      1 |00:00:00.01 |      14 |
|   1 |  SORT AGGREGATE    |                 |      1 |      1 |      1 |00:00:00.01 |      14 |
|*  2 |   FILTER           |                 |      1 |        |   3973 |00:00:00.01 |      14 |
|*  3 |    INDEX RANGE SCAN| IX_T_ORD_DT_AMT |      1 |    250 |   3973 |00:00:00.01 |      14 |
----------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_YM,'YYYYMM')<=LAST_DAY(TO_DATE(:V_YM,'YYYYMM')))
   3 - access("ORD_DT">=TO_DATE(:V_YM,'YYYYMM') AND "ORD_DT"<=LAST_DAY(TO_DATE(:V_YM,'YYYYMM')))

문제

이번 배포 판이 장부보다 137건 적게 세는 이유를 설명하시오.
오과장의 보정안 (가)와 (나)가 지금의 DATE 컬럼에서, 그리고 TIMESTAMP(3)로 바뀐 뒤에 각각 맞는 답을 내는지 따져 설명하시오.
컬럼 타입이 바뀌어도 맞는 답을 내고 인덱스 범위 스캔도 유지되도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD
 WHERE ORD_DT >= TO_DATE(:v_ym, 'YYYYMM')                   -- 이달 1일 0시 이상
   AND ORD_DT <  ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1)    -- 다음 달 1일 0시 미만 (끝값을 쓰지 않는다)

개선 후 실행계획 학습용 예시 · 건수는 교재 02화 실측

xplan

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |      1 |        |      1 |00:00:00.01 |      14 |
|   1 |  SORT AGGREGATE    |                 |      1 |      1 |      1 |00:00:00.01 |      14 |
|*  2 |   FILTER           |                 |      1 |        |   4110 |00:00:00.01 |      14 |
|*  3 |    INDEX RANGE SCAN| IX_T_ORD_DT_AMT |      1 |    250 |   4110 |00:00:00.01 |      14 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_YM,'YYYYMM')<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1))
   3 - access("ORD_DT">=TO_DATE(:V_YM,'YYYYMM') AND "ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1))

모범 답안 서술

LAST_DAY(TO_DATE('202406', 'YYYYMM'))는 6월 30일 00:00:00이다. LAST_DAY는 날짜만 그달 마지막 날로 옮기고 시각은 입력값(1일 0시)을 그대로 가져온다. 그래서 조건이 'BETWEEN 6월 1일 0시 AND 6월 30일 0시'가 되어, 시분초가 있는 6월 30일 주문 137건(34,159,700원)이 전부 빠진다. 3번 줄 access에서 상한이 <=LAST_DAY(...)인 것을 볼 수 있다. 인덱스는 잘 탔지만(Buffers 14) 답이 틀렸다. 보정안 (가)는 0.99999일이 86,399.136초라서 DATE에서는 23:59:59로 떨어져 지금은 맞는다. 하지만 9 하나를 빠뜨려 0.9999로 쓰면 23:59:51이 되어 마지막 8초가 빠지는 식으로, 맞는지가 숫자 계산에 달려 있다. (나)도 DATE에서는 맞는다. 둘 다 '초 단위까지만 있다'는 DATE의 성질에 기댄 상한이다. 컬럼이 TIMESTAMP(3)로 바뀌면 23:59:59.001 ~ 23:59:59.999에 들어온 주문은 상한 23:59:59보다 커서 빠진다. 건수는 적어도 대사가 맞지 않는 달이 생기고, 원인을 찾기도 어렵다. 그래서 끝값을 쓰지 않고 '이달 1일 0시 이상, 다음 달 1일 0시 미만'으로 준다. ORD_DT >= TO_DATE(:v_ym, 'YYYYMM') AND ORD_DT < ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1)은 DATE든 TIMESTAMP든 같은 순간을 경계로 쓰므로 타입이 바뀌어도 맞다. 컬럼을 가공하지 않으므로 INDEX RANGE SCAN도 그대로다. 6월은 4,110건으로 장부와 같고, 읽는 양은 14블록이다(TO_CHAR 판 896 → 14). 배포 전에는 원본(TO_CHAR 판)과 고친 판의 건수와 금액을 여러 달에 걸쳐 대조하고, 말일 23시 이후 주문처럼 경계에 있는 행을 일부러 넣어 회귀 검증한다.

해설

함정: 이번 배포 판은 BETWEEN이 문제가 아니라 상한 값이 틀렸다. ORD_DT <= LAST_DAY(...)로 바꿔도 똑같이 137건이 빠진다. 'BETWEEN 금지'만 외우지 말고 경계값이 실제로 몇 시인지 계산해 본다.
TIMESTAMP 컬럼을 DATE 값과 비교하면 바인드 쪽 DATE가 TIMESTAMP로 바뀌고 컬럼은 그대로다. 그래서 고친 SQL은 타입이 바뀐 뒤에도 인덱스 범위 스캔이 유지된다. 반대로 DATE 컬럼에 TIMESTAMP 바인드를 주면 컬럼 쪽이 바뀌어 인덱스를 못 쓴다(묵시적 형변환).
교재 02화 §3.5의 표와 같은 137건이다. 그때는 BETWEEN DATE '2024-06-01' AND DATE '2024-06-30'이었고, 이번에는 LAST_DAY가 같은 자정을 만들었다. 모양이 달라도 끝값이 자정이면 같은 사고가 난다.
이 테이블에서는 TO_CHAR 판이 느려도 답은 맞았다. 느린 코드를 빠르게 고칠 때는 같은 답을 내는지부터 확인한다(교재 02화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 교재 02화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 교재 02화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황