창고 태블릿의 「출고일별 주문」이 열 때마다 주문 테이블을 다 읽는다

중급SELECT한빛유통12 / 700

상황

한빛유통 물류센터는 18시에 그날 출고를 마감한다. 18시 정각부터 들어온 주문은 다음 날 출고분이다. 창고 팀장 태블릿 12대가 출고일을 골라 그날 내보낼 주문 목록을 수시로 연다. 개발자는 회계팀이 준 규칙 문서의 문장("18시 이후 주문은 주문일 + 1일을 출고일로 한다")을 그대로 SQL로 옮겼다. 아래는 출고일 2024년 6월 14일을 조회했을 때다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · ORD_DT DATE(시분초 있음) · 하루 136 ~ 137건이 서로 다른 블록에 흩어져 있다
출고일이 6월 14일인 주문(6월 13일 18:00:00 이상, 6월 14일 18:00:00 미만): 139건
인덱스 IX_T_ORD_DT (ORD_DT): BLEVEL 1 · 리프 265블록 · PK_T_ORD (ORD_SEQ) · 통계 최신

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 WHERE CASE WHEN TO_CHAR(ORD_DT, 'HH24') >= '18'
            THEN TRUNC(ORD_DT) + 1
            ELSE TRUNC(ORD_DT)
       END = TO_DATE(:v_ship_dt, 'YYYYMMDD')
 ORDER BY ORD_DT

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    139 |00:00:00.02 |     896 |          |
|   1 |  SORT ORDER BY     |       |      1 |   1000 |    139 |00:00:00.02 |     896 |18432  (0)|
|*  2 |   TABLE ACCESS FULL| T_ORD |      1 |   1000 |    139 |00:00:00.02 |     896 |          |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(CASE  WHEN TO_CHAR(INTERNAL_FUNCTION("ORD_DT"),'HH24')>='18' THEN
              TRUNC(INTERNAL_FUNCTION("ORD_DT"))+1 ELSE TRUNC(INTERNAL_FUNCTION("ORD_DT")) END
              =TO_DATE(:V_SHIP_DT,'YYYYMMDD'))

문제

실행계획의 어느 부분에서 인덱스를 쓰지 못하는 원인을 알 수 있는지 설명하시오.
규칙은 그대로 지키면서 ORD_DT에 대한 범위 조건 하나로 SQL을 고치시오. 18시 정각에 들어온 주문이 어느 출고일에 들어가는지 경계를 따져 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 WHERE ORD_DT >= TO_DATE(:v_ship_dt, 'YYYYMMDD') - 6/24    -- 전날 18:00:00 이상
   AND ORD_DT <  TO_DATE(:v_ship_dt, 'YYYYMMDD') + 18/24   -- 당일 18:00:00 미만
 ORDER BY ORD_DT

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

xplan

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |    139 |00:00:00.01 |     142 |
|*  1 |  FILTER                      |             |      1 |        |    139 |00:00:00.01 |     142 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORD       |      1 |    137 |    139 |00:00:00.01 |     142 |
|*  3 |    INDEX RANGE SCAN          | IX_T_ORD_DT |      1 |    137 |    139 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:V_SHIP_DT,'YYYYMMDD')-.25<TO_DATE(:V_SHIP_DT,'YYYYMMDD')+.75)
   3 - access("ORD_DT">=TO_DATE(:V_SHIP_DT,'YYYYMMDD')-.25 AND "ORD_DT"<TO_DATE(:V_SHIP_DT,'YYYYMMDD')+.75)

모범 답안 서술

2번 줄 filter를 보면 ORD_DT가 TO_CHAR와 TRUNC로 가공된 뒤 CASE 식에 들어가 있다. 이 식의 값은 행마다 계산해야 알 수 있고 인덱스에는 ORD_DT 원래 값만 정렬되어 있으므로, access로 범위를 지목하지 못하고 10만 행을 다 읽는다(Buffers 896). 함수 조건이라 E-Rows도 1,000(1%)으로 나왔다. 규칙을 주문 일시 쪽으로 풀어 보면, 출고일이 D인 주문은 전날 18:00:00 이상, 당일 18:00:00 미만에 들어온 주문이다. 18시 이후(HH24가 '18' 이상)는 다음 날로 넘어가므로 전날 18시부터는 D에 속하고, D의 18시부터는 D+1에 속한다. 그래서 ORD_DT >= D - 6/24 AND ORD_DT < D + 18/24로 쓰면 결과가 같다. 18시 정각 주문은 다음 날 출고분이므로 아래 끝은 >=로 포함하고 위 끝은 <로 뺀다. 양 끝은 바인드 변수 쪽에서 계산하므로 컬럼은 가공되지 않는다. 고친 뒤에는 3번 줄이 INDEX RANGE SCAN이 되어 하루치 범위만 읽는다. 행들이 블록 139개에 흩어져 있어 테이블은 행마다 한 블록씩 읽지만 Buffers는 896 → 142로 준다. 인덱스 순서가 곧 ORD_DT 순서라 SORT ORDER BY도 사라진다.

해설

함정: 위 끝을 BETWEEN … AND D + 18/24 - 1/86400(17:59:59)로 쓰면 DATE에서는 맞지만, 주문 시각이 TIMESTAMP로 바뀌면 17:59:59.5 같은 주문이 빠진다. 범위는 늘 '이상, 미만'으로 준다(IDX-013).
마감 시각이 센터마다 다르거나 바뀔 수 있다면 그 값을 코드 테이블에 두고 바인드 쪽에서 양 끝을 계산한다. 조건이 컬럼 쪽에 남지 않는 한 규칙이 바뀌어도 인덱스는 그대로 쓰인다.
SQL을 고칠 수 없다면 CASE 식 그대로 함수 기반 인덱스를 만들 수 있다. 하지만 마감 시각이 17시로 바뀌는 순간 SQL과 인덱스를 함께 다시 만들어야 하고, 식이 글자 하나만 달라도 쓰이지 않는다(교재 03화 §4.2).
업무 규칙 문장을 그대로 옮기면 컬럼 쪽에 식이 생기기 쉽다. 규칙을 '컬럼이 어느 범위에 있으면 되는가'로 다시 쓰는 것이 이 문제의 핵심이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황