인덱스는 있는데 기간 조회가 FULL로 돈다

기초SELECT한빛유통01 / 700

상황

정산 화면의 「기간 매출」 조회는 하루에 수천 번 호출된다. 개발팀은 이렇게 말한다. "ORD_DT에 인덱스를 만들었는데 안 빨라졌다." 화면은 'YYYYMMDD' 형식의 문자열 두 개(:v_from, :v_to)를 넘긴다. 아래는 2024년 6월 한 달을 넘겼을 때의 실행계획이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT는 DATE(전부 자정, 시분초 없음) · 6월 한 달 27,390행
인덱스 IX_ORD_BIG_DT (ORD_DT): 이미 있다 · 통계 최신

원본 SQL

sql

SELECT COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 WHERE TO_CHAR(ORD_DT, 'YYYYMMDD') BETWEEN :v_from AND :v_to

실행계획 실측 · 교재 02화

xplan

Plan hash value: 914281838

------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.11 |    5826 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.11 |    5826 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   2500 |  27390 |00:00:00.11 |    5826 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((TO_CHAR(INTERNAL_FUNCTION("ORD_DT"),'YYYYMMDD')>=:V_FROM AND
              TO_CHAR(INTERNAL_FUNCTION("ORD_DT"),'YYYYMMDD')<=:V_TO))

문제

인덱스가 있는데도 TABLE ACCESS FULL로 처리되는 이유를 실행계획의 어느 줄에서 알 수 있는지 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 인덱스 범위 스캔이 되도록 하시오. 읽는 양을 더 줄일 수 있다면 인덱스 설계도 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

-- 테이블에 가지 않고 인덱스만으로 끝나게 (SUM 대상 ORD_AMT 까지 담는다)
CREATE INDEX IX_ORD_BIG_DT_AMT ON T_ORD_BIG (ORD_DT, ORD_AMT);

개선 후 실행계획 실측 · 교재 02화

xplan

Plan hash value: 92368507

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.01 |      91 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.01 |      91 |
|*  2 |   FILTER           |                   |      1 |        |  27390 |00:00:00.01 |      91 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_DT_AMT |      1 |  27390 |  27390 |00:00:00.01 |      91 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

모범 답안 서술

실행계획 2번 줄의 조건은 access가 아니라 filter이고, TO_CHAR(INTERNAL_FUNCTION("ORD_DT"))처럼 컬럼이 함수로 가공되어 있다. 인덱스에는 ORD_DT가 날짜 값 그대로 정렬되어 있어서, 가공한 값으로는 "여기부터 여기까지"를 지목할 수 없다. 그래서 100만 행을 다 읽고 버린다(Buffers 5,826). 컬럼은 그대로 두고 바인드 변수 쪽을 TO_DATE로 바꿔 ORD_DT >= 시작일 AND ORD_DT < 종료일 + 1로 준다. 상한을 < 다음 날로 주면 시분초가 있는 날짜에서도 마지막 날이 빠지지 않는다. 집계 대상인 ORD_AMT까지 담은 (ORD_DT, ORD_AMT) 인덱스를 만들면 테이블 액세스 없이 인덱스 범위만 읽는다(Buffers 5,826 → 91).

해설

함정: WHERE ORD_DT BETWEEN TO_DATE(:v_from) AND TO_DATE(:v_to)로 고치면 이 테이블(전부 자정)에서는 답이 같아서 테스트를 통과한다. 그런데 시분초가 있는 T_ORD에 같은 코드를 쓰면 마지막 날 0시 이후의 주문이 사라진다(교재 02화: 137건, 34,159,700원). 상한은 < 다음 날로 주는 것이 안전하다.
인덱스를 하나 더 만드는 대가: 커버링 인덱스는 조회 비용을 줄이지만, 적재하거나 갱신할 때마다 인덱스를 하나 더 고쳐야 한다(교재 04화, 14화). 리포트에 적재 비용을 함께 적는다.
함수 기반 인덱스 (TO_CHAR(ORD_DT,'YYYYMMDD'))도 답이 된다. 하지만 SQL 모양이 정확히 같아야만 쓰이고, 컬럼을 가공하는 방식을 굳혀 버린다. SQL을 고칠 수 있으면 SQL을 고친다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 02화 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 02화 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황