PAY_DT에 인덱스를 만들었는데 미결제 독촉은 여전히 FULL이다

중급DDL한빛유통04 / 700

상황

결제팀의 「미결제 독촉」은 10분마다 결제되지 않은 주문을 뽑아 알림톡을 보낸다. 지난주 조민우가 PAY_DT에 인덱스를 만들었는데 실행계획은 그대로 FULL이다. INDEX 힌트를 줘도 무시됐다. 조민우는 어디선가 들은 말("IS NULL은 인덱스를 못 탄다")을 믿고 NVL로 바꿔 봤다. 그래도 FULL이다. 두 판의 실행계획이 모두 아래에 있다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · CUS_ID NOT NULL
PAY_DT DATE: 결제 시각 · 결제 전이면 NULL · NOT NULL 제약 없음 · 미결제는 늘 수십 건이고 지금은 41건(통계의 NUM_NULLS 41) (문제 설정: 교재의 원본 T_ORD에는 PAY_DT가 빈 행이 없다. 2024.12 결제 연동 개편 뒤의 모습이라고 본다)
인덱스 IX_T_ORD_PAY (PAY_DT): 지난주 생성 · BLEVEL 1 · 리프 265블록 · 통계 최신

원본 SQL

sql

-- [원본]
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 WHERE PAY_DT IS NULL;

-- [조민우의 수정안]
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 WHERE NVL(PAY_DT, TO_DATE('99991231', 'YYYYMMDD')) = TO_DATE('99991231', 'YYYYMMDD');

실행계획 학습용 예시

xplan

[원본 — PAY_DT IS NULL]
-------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |     41 |00:00:00.01 |     896 |
|*  1 |  TABLE ACCESS FULL| T_ORD |      1 |     41 |     41 |00:00:00.01 |     896 |
-------------------------------------------------------------------------------------
   1 - filter("PAY_DT" IS NULL)

[조민우의 수정안 — NVL]
-------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |     41 |00:00:00.01 |     896 |
|*  1 |  TABLE ACCESS FULL| T_ORD |      1 |   1000 |     41 |00:00:00.01 |     896 |
-------------------------------------------------------------------------------------
   1 - filter(NVL("PAY_DT",TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))=
              TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

문제

원본은 E-Rows가 41로 정확한데도 인덱스를 쓰지 못한다. 그 이유를 설명하고, 조민우의 NVL 판도 FULL로 처리되는 이유를 설명하시오.
SQL은 PAY_DT IS NULL 그대로 두고, 인덱스로 41건만 찾아가도록 DDL을 작성하시오. 다른 방법 하나와 그 대가도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 단일 컬럼 인덱스는 PAY_DT 가 NULL 인 행을 담지 않는다 → 상수 0 을 뒤에 붙여 모든 행을 담는다
DROP INDEX IX_T_ORD_PAY;
CREATE INDEX IX_T_ORD_PAY ON T_ORD (PAY_DT, 0);

-- SQL 은 원본 그대로 (컬럼을 감싸지 않는다)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 WHERE PAY_DT IS NULL;

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |     41 |00:00:00.01 |      43 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD        |      1 |     41 |     41 |00:00:00.01 |      43 |
|*  2 |   INDEX RANGE SCAN                  | IX_T_ORD_PAY |      1 |     41 |     41 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------
   2 - access("PAY_DT" IS NULL)

모범 답안 서술

원본 1번 줄은 filter("PAY_DT" IS NULL)이다. 통계가 NULL 41건을 알고 있어서 E-Rows는 정확하다. 하지만 B-tree 인덱스는 키 컬럼이 전부 NULL인 행을 저장하지 않는다. IX_T_ORD_PAY에는 결제된 99,959건만 들어 있고 미결제 41건은 어디에도 없다. 찾아갈 곳이 없으니 힌트도 무시되고 테이블을 다 읽는다(Buffers 896). 조민우의 NVL 판은 컬럼을 함수로 감쌌다. IDX-001의 TO_CHAR와 같은 컬럼 가공이라 인덱스를 쓰지 못하고, 추정치까지 1,000건(함수 조건의 기본값 1%)으로 틀어졌다. 결합 인덱스는 키 컬럼 중 하나라도 NULL이 아니면 그 행을 저장한다. PAY_DT 뒤에 상수 0을 붙여 (PAY_DT, 0)으로 다시 만들면 모든 행이 인덱스에 들어간다. NULL은 정렬의 맨 끝에 모이므로 access("PAY_DT" IS NULL)로 찾아갈 수 있다(Buffers 896 → 43). 상수 대신 NOT NULL 컬럼(ORD_SEQ, CUS_ID)을 뒤에 붙여도 된다. 대가도 있다. 인덱스가 10만 행을 다 담으므로 리프가 265 → 약 293블록으로 커지고, 주문을 INSERT하거나 결제를 UPDATE할 때마다 이 인덱스를 고친다(적재 비용). 다른 방법은 둘이다. ① 미결제만 담는 함수 기반 인덱스 (CASE WHEN PAY_DT IS NULL THEN 'N' END): 41행짜리라 한 블록이면 되지만, SQL이 인덱스와 같은 식을 써야만 탄다. ② 근본적으로는 모델을 고친다. 결제 상태 컬럼(PAY_ST)을 두고 NOT NULL 제약과 기본값을 주면 NULL로 상태를 표현하지 않아도 된다.

해설

함정 ①: PAY_DT IS NULL을 PAY_DT = NULL로 '고치면' 결과가 0건이 된다. NULL은 =로 비교할 수 없다.
함정 ②: (CUS_ID, PAY_DT)처럼 NOT NULL 컬럼을 앞에 붙이면 행은 다 담기지만, PAY_DT IS NULL만으로는 선두 조건이 없다(IDX-006). 붙이는 컬럼은 뒤에 둔다.
NULL 행을 담지 않는 성질이 약점만은 아니다. 값이 있는 행이 소수인 컬럼이라면 단일 인덱스가 작게 유지된다. 미결제 플래그를 '미결제면 Y, 아니면 NULL'로 두는 설계가 그 응용이다(위 ①).
교재 02화 무력화 ⑤: city IS NULL은 FULL 126이다. 교정형은 NOT NULL 제약과 기본값, 또는 (col, 0) 복합 인덱스다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황