상담원 30명의 「오늘 주문」 상황판이 1분마다 주문 테이블을 다 읽는다

기초SELECT한빛유통08 / 700

상황

고객센터 상황판 맨 위 칸은 오늘 들어온 주문을 최신순으로 보여 준다. 상담원 PC 30대가 1분마다 새로 고친다. 조민우는 이렇게 말한다. "ORD_DT에 시분초가 있어서 TRUNC로 날짜만 맞췄어요. 형변환도 아니고 날짜를 자르기만 했는데, 이것도 가공인가요?" 아래는 2024년 6월 14일 밤(그날 주문 137건)에 잰 계획이다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · ORD_DT DATE(시분초 있음, 2023-01-01 ~ 2024-12-30) · 하루 136 ~ 137건
적재 순서가 날짜 순이 아니어서 하루치 주문 137건은 서로 다른 블록 137개에 흩어져 있다
인덱스 PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT): BLEVEL 1 · 리프 265블록 · 통계 최신

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD
 WHERE TRUNC(ORD_DT) = TRUNC(SYSDATE)
 ORDER BY ORD_DT DESC

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(INTERNAL_FUNCTION("ORD_DT"))=TRUNC(SYSDATE@!))

문제

TRUNC는 형변환이 아닌데도 IX_T_ORD_DT를 쓰지 못하는 이유를 실행계획의 어느 줄에서 알 수 있는지 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 인덱스 범위 스캔이 되도록 하시오. 정렬(SORT ORDER BY)이 어떻게 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD
 WHERE ORD_DT >= TRUNC(SYSDATE)          -- 오늘 0시 이상
   AND ORD_DT <  TRUNC(SYSDATE) + 1      -- 내일 0시 미만 (자르는 일은 SYSDATE 쪽에서)
 ORDER BY ORD_DT DESC

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TRUNC(SYSDATE@!)+1>TRUNC(SYSDATE@!))
   3 - access("ORD_DT"<TRUNC(SYSDATE@!)+1 AND "ORD_DT">=TRUNC(SYSDATE@!))

모범 답안 서술

2번 줄 조건은 filter(TRUNC(INTERNAL_FUNCTION("ORD_DT"))=TRUNC(SYSDATE@!))이다. TRUNC는 형변환은 아니지만 컬럼 값을 다른 값으로 바꾸는 함수라서 가공이다. IX_T_ORD_DT에는 시각까지 붙은 ORD_DT 원래 값이 정렬되어 있고, 자정으로 잘린 값은 인덱스 어디에도 없다. 그래서 access로 범위를 지목하지 못하고 10만 행을 다 읽는다(Buffers 896). 함수 조건이라 E-Rows도 1,000(1%)으로 나와 실제 137건과 어긋난다. 자르는 일을 컬럼이 아니라 SYSDATE 쪽에서 하면 된다. '오늘'은 ORD_DT >= TRUNC(SYSDATE) AND ORD_DT < TRUNC(SYSDATE) + 1이고, 결과는 TRUNC 판과 같다. 상한은 BETWEEN이 아니라 < 내일 0시로 준다. 고친 뒤에는 3번 줄이 오늘 범위만 읽는 INDEX RANGE SCAN이 된다. 인덱스를 거꾸로(DESCENDING) 읽으면 행이 ORD_DT 역순으로 나오므로 SORT ORDER BY도 사라진다. 하루치 137건이 블록 137개에 흩어져 있어 테이블은 행마다 한 블록씩 읽지만, Buffers는 896 → 140으로 준다. 상담원 PC 30대가 1분마다 부르므로 하루로 치면 약 3,870만 → 약 600만 블록이다.

해설

함정: ORD_DT BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 1은 내일 0시 정각 주문까지 담는다. BETWEEN은 양 끝을 포함하므로 상한은 <로 준다.
TRUNC를 쓰는 것 자체가 나쁜 것이 아니다. SELECT 절이나 GROUP BY TRUNC(ORD_DT)처럼 조건이 아닌 곳, 그리고 SYSDATE나 바인드 변수 쪽에 쓰는 TRUNC는 인덱스와 상관이 없다. 막히는 것은 조건절의 컬럼 쪽에 씌울 때다.
함수 기반 인덱스 (TRUNC(ORD_DT))를 만들면 SQL을 고치지 않아도 되지만 인덱스가 하나 늘고, 인덱스가 날짜 단위로만 정렬되어 있어 같은 날 안의 시각 역순 정렬은 여전히 따로 해야 한다.
하루치는 140블록이지만 같은 인덱스로 한 달(4,110건)을 읽으면 테이블 블록을 4천 번 가까이 찾아가 FULL(896)보다 비싸진다. 흩어진 테이블에서 인덱스가 언제까지 이득인지는 교재 04화(손익분기점)에서 다룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황