인덱스 힌트까지 넣었는데 결제 모니터의 한 시간치 조회가 인덱스를 처음부터 끝까지 읽는다

중급SELECT한빛유통23 / 700

상황

결제팀의 「결제 대기 모니터」는 10분마다 직전 한 시간의 주문을 뽑아 결제 상태를 확인한다. 결제팀 화면 여섯 대가 띄워 두고 있어 하루 860번쯤 돈다. 지난주 조민우가 ORD_DT에 인덱스를 만들었는데 계획은 그대로 FULL이었다. 그래서 INDEX 힌트를 넣었더니 이번에는 인덱스를 처음부터 끝까지 읽는다. 자바 코드는 기간을 java.sql.Timestamp로 만들어 ps.setTimestamp로 넘긴다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · ORD_DT DATE(시분초 있음, 2023-01-01 ~ 2024-12-31) · 한 시간에 약 6건 · PAY_DT · PAY_AMT
인덱스 PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT): 지난주 생성 · BLEVEL 1 · 리프 265블록 · 통계 최신
같은 날짜의 주문은 테이블 곳곳에 흩어져 있다(주문 6건이면 테이블 블록 6개)

원본 SQL

sql

-- [원본]
SELECT ORD_SEQ, CUS_ID, ORD_DT, PAY_DT, PAY_AMT
  FROM T_ORD
 WHERE ORD_DT >= :1
   AND ORD_DT <  :2
 ORDER BY ORD_DT;

-- [조민우의 시도]
SELECT /*+ INDEX(T_ORD IX_T_ORD_DT) */
       ORD_SEQ, CUS_ID, ORD_DT, PAY_DT, PAY_AMT
  FROM T_ORD
 WHERE ORD_DT >= :1
   AND ORD_DT <  :2
 ORDER BY ORD_DT;

실행계획 학습용 예시

xplan

[원본 · 2024-06-14 09:00 ~ 10:00]
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |      6 |00:00:00.02 |     896 |
|   1 |  SORT ORDER BY              |             |      1 |    250 |      6 |00:00:00.02 |     896 |
|*  2 |   TABLE ACCESS FULL         | T_ORD       |      1 |    250 |      6 |00:00:00.02 |     896 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((INTERNAL_FUNCTION("ORD_DT")>=:1 AND INTERNAL_FUNCTION("ORD_DT")<:2))

[조민우의 시도 · INDEX 힌트]
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |      6 |00:00:00.03 |     272 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD       |      1 |    250 |      6 |00:00:00.03 |     272 |
|*  2 |   INDEX FULL SCAN           | IX_T_ORD_DT |      1 |    250 |      6 |00:00:00.03 |     266 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((INTERNAL_FUNCTION("ORD_DT")>=:1 AND INTERNAL_FUNCTION("ORD_DT")<:2))

[V$SQL_BIND_CAPTURE]
NAME  DATATYPE_STRING  VALUE_STRING
----  ---------------  -----------------------------
:1    TIMESTAMP        2024-06-14 09:00:00.000000000
:2    TIMESTAMP        2024-06-14 10:00:00.000000000

문제

Predicate Information의 INTERNAL_FUNCTION("ORD_DT")가 무엇을 뜻하는지, 왜 컬럼 쪽에 붙었는지 바인드 정보로 설명하시오.
힌트를 넣은 판이 INDEX RANGE SCAN이 아니라 INDEX FULL SCAN이 된 이유를 설명하시오.
한 시간 범위만 읽도록 SQL을 고치고, 자바 쪽에서 고칠 때 주의할 점을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 바인드(TIMESTAMP)를 DATE로 바꿔 비교한다. 컬럼 ORD_DT는 그대로 → IX_T_ORD_DT 범위 스캔 · 힌트는 뗀다
SELECT ORD_SEQ, CUS_ID, ORD_DT, PAY_DT, PAY_AMT
  FROM T_ORD
 WHERE ORD_DT >= CAST(:1 AS DATE)
   AND ORD_DT <  CAST(:2 AS DATE)
 ORDER BY ORD_DT;

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

xplan

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(CAST(:2 AS DATE)>CAST(:1 AS DATE))
   3 - access("ORD_DT">=CAST(:1 AS DATE) AND "ORD_DT"<CAST(:2 AS DATE))

모범 답안 서술

INTERNAL_FUNCTION은 오라클이 붙인 내부 변환 함수다. V$SQL_BIND_CAPTURE를 보면 두 바인드가 TIMESTAMP다. DATE와 TIMESTAMP를 비교하면 오라클은 정밀도가 높은 TIMESTAMP 쪽에 맞춰 DATE 쪽을 바꾸는데, 그 DATE 쪽이 컬럼 ORD_DT였다. 그래서 2번 줄의 조건이 filter(INTERNAL_FUNCTION("ORD_DT")>=:1 …)가 됐고, 인덱스에서 범위를 지목하지 못해 10만 행을 다 읽었다(Buffers 896). INDEX 힌트는 이 인덱스를 쓰라는 지시일 뿐, 없는 access 조건을 만들어 주지 못한다. 그래서 옵티마이저는 정렬 순서를 얻으려고 리프 265블록을 처음부터 끝까지 훑는 INDEX FULL SCAN을 고르고, 항목마다 같은 변환을 해서 거른다(Buffers 272). 인덱스를 "썼지만" 찾아간 것이 아니다. 컬럼은 그대로 두고 바인드 쪽을 CAST(:1 AS DATE)로 바꾼다. 그러면 access("ORD_DT">=CAST(:1 AS DATE) …)로 한 시간 범위만 읽고, 인덱스가 이미 시각 순이라 정렬도 없다(Buffers 896 → 8). 힌트는 뗀다. 자바 쪽에서 고칠 때는 setDate(java.sql.Date)로 바꾸지 않는다. java.sql.Date는 날짜만 담는 타입이라 시각이 0시로 잘려 한 시간 범위가 엉뚱한 범위가 된다. 시각까지 담는 DATE 타입 바인드(Oracle JDBC의 oracle.sql.DATE)를 쓰거나, SQL의 CAST로 맞춘다.

해설

규칙: DATE와 TIMESTAMP를 비교하면 DATE 쪽이 TIMESTAMP로 바뀐다. 컬럼이 DATE이고 값이 TIMESTAMP면 컬럼이 바뀌어 인덱스를 못 쓴다. 반대로 컬럼이 TIMESTAMP이고 값이 DATE면 값이 바뀌므로 문제가 없다.
CAST의 대가: TIMESTAMP를 DATE로 바꾸면 소수 초가 사라진다. 이 화면은 정각 단위라 상관없지만, 밀리초 단위 경계를 쓰는 화면이라면 경계값을 다시 검증한다.
프레임워크가 날짜를 모두 TIMESTAMP로 바인드한다면 같은 증상이 DATE 컬럼 조회 전체에 퍼져 있을 수 있다. V$SQL_PLAN의 조건에서 INTERNAL_FUNCTION을 찾아 한꺼번에 점검한다.
함수 기반 인덱스로 받으려 하지 않는다. 고칠 곳은 바인드 타입 하나다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황