쿠폰 쓴 판매 8천 건을 세는데 판매 50만 건을 다 읽는다

기초SELECT유통37 / 700

상황

유통 본사 마케팅팀의 「모바일 쿠폰 캠페인 실적」 화면은 캠페인별로 쿠폰을 쓴 판매 건수를 보여 준다. 캠페인 담당 네 명이 열어 두고, 화면 위쪽 숫자는 5분마다 새로 고친다. 지난달 DBA가 CPN_NO에 인덱스를 만들었는데 계획은 그대로다. 개발자는 "쿠폰을 안 쓴 판매는 NULL이라서 NVL로 걸러 냈다"라고 한다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,510)
CPN_NO VARCHAR2(10): 모바일 쿠폰 캠페인 번호('MC2306A') · 쿠폰을 쓴 판매에만 값이 있고 나머지는 NULL · 값이 있는 행 7,812건(1.6%) · 캠페인 24종 · 'NONE'이라는 캠페인 번호는 없다 (문제 설정: 교재의 CF10에는 이 컬럼이 없다. 2023년 모바일 쿠폰을 도입하며 더한 컬럼이라고 본다)
인덱스 IX_CF10_CPN (CPN_NO): 지난달 생성 · BLEVEL 1 · 리프 23블록 · 통계 최신(NUM_NULLS 492,188)

원본 SQL

sql

SELECT CPN_NO, COUNT(*) AS use_cnt
  FROM CF10
 WHERE NVL(CPN_NO, 'NONE') <> 'NONE'
 GROUP BY CPN_NO

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     24 |00:00:00.05 |    4510 |
|   1 |  HASH GROUP BY     |      |      1 |     24 |     24 |00:00:00.05 |    4510 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |  25000 |   7812 |00:00:00.05 |    4510 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NVL("CPN_NO",'NONE')<>'NONE')

문제

인덱스가 있는데도 테이블 전체를 읽는 이유를 설명하시오. 판매 50만 건짜리 테이블인데 IX_CF10_CPN의 리프가 23블록밖에 안 되는 이유도 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 인덱스만 읽도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT CPN_NO, COUNT(*) AS use_cnt
  FROM CF10
 WHERE CPN_NO IS NOT NULL          -- 쿠폰을 쓴 판매만 (인덱스에 든 행이 곧 답이다)
 GROUP BY CPN_NO

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |      1 |        |     24 |00:00:00.01 |      25 |
|   1 |  SORT GROUP BY NOSORT|             |      1 |     24 |     24 |00:00:00.01 |      25 |
|   2 |   INDEX FULL SCAN    | IX_CF10_CPN |      1 |   7812 |   7812 |00:00:00.01 |      25 |
---------------------------------------------------------------------------------------------

모범 답안 서술

2번 줄의 조건은 filter(NVL("CPN_NO",'NONE')<>'NONE')이다. NVL로 컬럼을 감싸면 컬럼 가공이 되고, 옵티마이저 입장에서는 CPN_NO가 NULL인 행도 NVL을 거쳐 조건을 따져 봐야 하는 후보가 된다. 그런데 그 행들은 인덱스에 없다. 그래서 인덱스만으로는 답할 수 없다고 보고 테이블 50만 행을 다 읽는다(Buffers 4,510). B-tree 인덱스는 키가 NULL인 행을 담지 않는다. 쿠폰을 안 쓴 판매 492,188건은 IX_CF10_CPN에 들어가지 않고, 쿠폰을 쓴 7,812건만 들어 있다. 판매 전체를 담은 IX_CF10_SLE_DT의 리프가 1,393블록인데 이 인덱스가 23블록인 이유다. 조건을 CPN_NO IS NOT NULL로 쓰면 답이 되는 행이 모두 인덱스에 있다는 것이 보장된다. 옵티마이저는 리프 23블록을 처음부터 끝까지 읽고(INDEX FULL SCAN), 인덱스가 CPN_NO 순으로 정렬되어 있으므로 정렬 없이 캠페인별로 센다(SORT GROUP BY NOSORT). Buffers는 4,510 → 25로 준다. 5분마다 새로 고치므로 하루로 치면 약 130만 → 7천 블록이다. 결과도 같다. NVL(CPN_NO, 'NONE') <> 'NONE'은 'CPN_NO가 NULL이 아니고 NONE도 아니다'라는 뜻인데, NONE이라는 캠페인 번호는 없으므로(0건 확인) CPN_NO IS NOT NULL과 같은 행을 고른다.

해설

WHERE CPN_NO <> 'NONE'처럼 NVL만 떼어도 인덱스를 쓸 수 있다. NULL인 행은 <> 비교에서 참이 될 수 없으므로 어차피 답이 아니고, 옵티마이저도 작은 인덱스만 훑으면 된다고 판단한다. 부정형이라고 늘 인덱스를 못 쓰는 것은 아니다.
NULL이 대부분인 컬럼의 단일 인덱스는 '값이 있는 소수'만 담는 작은 인덱스가 된다. 반대로 쿠폰을 안 쓴 판매(CPN_NO IS NULL, 98%)는 이 인덱스로 찾을 수 없고, 고르는 비율이 커서 찾을 이유도 없다.
쿠폰 미사용을 'NONE' 같은 값으로 채우는 모델 변경은 이 인덱스를 50만 행짜리로 키운다. 이 조회에는 NULL로 두는 편이 유리하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황