판매일 인덱스가 있는데 기간별 판매 전표 조회가 FULL로 돌고 1초 가까이 걸린다

기초SELECT유통678 / 700

상황

본사 회계팀의 「기간별 판매 전표」 화면은 시작일과 종료일을 달력에서 고르고, Java가 두 값을 DATE 바인드(setDate)로 넘긴다. 한 달을 고르면 1초 가까이 걸린다. SLE_DT가 문자 컬럼이라서 개발자는 공통 함수 FN_STR2DT로 판매일을 날짜로 바꿔 비교했다. 개발자의 설명은 "형변환을 직접 했으니 묵시적 형변환 문제는 없다"는 것이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) '20231201' · 2022 ~ 2023년 · 2023년 12월 20,864건
인덱스 IX_CF10_SLE_DT (SLE_DT): 이 문제에서는 있다 · 리프 1,393블록 · 통계 최신
FN_STR2DT: SQL이 없는 PL/SQL 함수 · 날짜로 바꿀 수 없는 문자열이면 NULL을 돌려준다 · 다른 화면 10여 곳도 쓴다
이번 주에 전수 점검한 결과 SLE_DT에는 날짜로 바꿀 수 없는 값이 없다

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_STR2DT(p_ymd VARCHAR2) RETURN DATE IS
BEGIN
  RETURN TO_DATE(p_ymd, 'YYYYMMDD');
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;          -- 잘못된 날짜 문자열은 NULL
END;
/

-- :d_fr = 2023-12-01, :d_to = 2023-12-31 (DATE 바인드)
SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, h.SHP_NO
  FROM CF10 h
 WHERE FN_STR2DT(h.SLE_DT) BETWEEN :d_fr AND :d_to;

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (ALLSTATS LAST) · 2023-12-01 ~ 2023-12-31]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  20864 |00:00:00.94 |    4583 |
|*  1 |  TABLE ACCESS FULL | CF10 |      1 |   1250 |  20864 |00:00:00.94 |    4583 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("PUP"."FN_STR2DT"("H"."SLE_DT")>=:D_FR AND "PUP"."FN_STR2DT"("H"."SLE_DT")<=:D_TO))

[V$SQL: 이 문장]         ELAPSED_TIME 0.94 초 · CPU_TIME 0.92 초 · PLSQL_EXEC_TIME 0.71 초 · BUFFER_GETS 4,583
[DBMS_HPROF: FN_STR2DT]  호출 520,864번 (함수 안에 SQL이 없어 recursive calls는 늘지 않는다)

문제

SLE_DT에 인덱스가 있는데도 테이블 전체를 읽는 이유를 Predicate Information으로 설명하시오. 함수가 행 수(500,000)보다 많은 520,864번 호출된 이유도 쓰시오.
결과가 바뀌지 않게 SQL을 고쳐 인덱스 범위 스캔이 되도록 하시오. 고친 SQL이 원래 SQL과 같은 결과를 내려면 무엇이 전제되어야 하는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 컬럼은 그대로 두고, 화면이 넘긴 DATE 를 컬럼 형식('YYYYMMDD' 문자열)으로 바꾼다
SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, h.SHP_NO
  FROM CF10 h
 WHERE h.SLE_DT BETWEEN TO_CHAR(:d_fr, 'YYYYMMDD') AND TO_CHAR(:d_to, 'YYYYMMDD');

-- 결과가 같다는 전제의 점검 (이번 주 전수 점검: 0건)
--   SELECT COUNT(*) FROM CF10 WHERE FN_STR2DT(SLE_DT) IS NULL;

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

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |  20864 |00:00:00.03 |     295 |
|*  1 |  FILTER                              |                |      1 |        |  20864 |00:00:00.03 |     295 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  20833 |  20864 |00:00:00.02 |     295 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20833 |  20864 |00:00:00.01 |      98 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(:D_FR,'YYYYMMDD')<=TO_CHAR(:D_TO,'YYYYMMDD'))
   3 - access("H"."SLE_DT">=TO_CHAR(:D_FR,'YYYYMMDD') AND "H"."SLE_DT"<=TO_CHAR(:D_TO,'YYYYMMDD'))

[V$SQL]  PLSQL_EXEC_TIME 0 · 함수 호출 0 · 결과 20,864행 (원래 SQL과 MINUS 양방향 0행)

모범 답안 서술

Predicate Information 1번 줄은 filter("PUP"."FN_STR2DT"("H"."SLE_DT")>=:D_FR AND …)이다. 함수 안에서 형변환을 했으니 묵시적 형변환은 없지만, 컬럼이 함수로 감싸여 가공된 것은 같다. 인덱스에는 SLE_DT 문자열이 그대로 정렬되어 있어서, 함수가 돌려줄 날짜로는 인덱스에서 범위를 찾을 수 없다. 그래서 access가 아니라 filter가 되고 50만 행을 FULL로 읽는다(Buffers 4,583). 함수 조건이라 E-Rows도 기본 선택도로 1,250이 나왔다. 함수는 filter를 평가할 때 행마다 호출된다. BETWEEN은 >=와 <= 두 조건이라, 11월 이전 479,136행은 첫 조건에서 떨어져 한 번만 부르고 12월 20,864행은 두 조건을 다 거쳐 두 번 부른다. 479,136 + 20,864 × 2 = 520,864번이다. 함수 안에 SQL이 없으니 recursive calls에도 Buffers에도 보이지 않고, V$SQL의 PLSQL_EXEC_TIME 0.71초로만 드러난다. SQL에서 PL/SQL로 52만 번 건너간 CPU다. 고칠 쪽은 컬럼이 아니라 화면이 넘긴 값이다. DATE 바인드를 TO_CHAR(:d_fr, 'YYYYMMDD')처럼 컬럼 형식의 문자열로 바꿔 SLE_DT BETWEEN … AND …로 주면 IX_CF10_SLE_DT를 범위 스캔한다. SLE_DT는 시각이 없는 8자리 날짜라 종료일까지 문자열 비교로 빠짐없이 들어온다. Buffers는 4,583 → 295, 경과는 0.94 → 0.03초가 되고 함수 호출은 0번이다. 결과가 같으려면 SLE_DT에 날짜로 바꿀 수 없는 잘못된 날짜 문자열이 없어야 한다. 원래 SQL에서는 함수가 그런 행을 NULL로 바꿔 조건에서 걸러 냈지만, 문자열 범위 비교는 범위 안에 들어오는 값이면 남긴다. 이번 주 전수 점검에서 그런 값이 0건이었으므로 두 SQL의 결과는 같다(MINUS 양방향 0행).

해설

함정: TO_DATE(h.SLE_DT, 'YYYYMMDD') BETWEEN :d_fr AND :d_to로 바꾸면 PL/SQL 호출은 사라지지만 컬럼 가공은 그대로라 여전히 FULL이다(0.94초 → 0.2초 안팎). 함수를 내장 함수로 바꾸는 것이 아니라 가공을 바인드 쪽으로 옮겨야 한다.
화면이 처음부터 '20231201' 같은 문자열을 setString으로 넘기는 것도 답이다. 컬럼 타입에 맞춰 값을 넘기는 것이 가장 단순하다(교재 03화).
함수 기반 인덱스 (FN_STR2DT(SLE_DT))도 쓸 수는 있다. 함수가 인자만 보므로 DETERMINISTIC을 붙일 수 있지만, 판매일 인덱스가 둘이 되고 판매를 넣을 때마다 함수가 한 번 더 돈다. SQL을 고칠 수 있으면 SQL을 고친다.
잘못된 값이 섞여 있다면 문자열 비교로 바꾸기 전에 데이터부터 고친다. 예를 들어 '20230231'은 함수에서는 NULL이라 빠지지만, 2월부터 3월까지의 문자열 범위에는 들어온다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황