고액 주문 알림이 1분마다 인덱스를 통째로 읽는다

기초SELECT한빛유통09 / 700

상황

리스크팀 모니터의 「고액 주문」 칸은 부가세를 포함한 금액이 기준(:v_min, 지금은 539,000원) 이상인 주문의 건수와 합계를 띄운다. 화면 여섯 대가 1분마다 새로 고친다. 조민우가 지난주 ORD_AMT에 인덱스를 만들었고, 실행계획에도 그 인덱스 이름이 나온다. 그런데 한 번에 2천 블록이 넘는다. "인덱스는 탔는데 왜 이렇게 많이 읽죠?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_AMT는 부가세 별도 금액(10,000 ~ 500,000원, 100원 단위로 고르게 퍼져 있다) · NOT NULL 제약은 없지만 NULL은 0건
부가세 포함 539,000원 이상, 즉 ORD_AMT 490,000원 이상인 주문: 20,512건(2.05%)
인덱스 IX_ORD_BIG_AMT (ORD_AMT): 지난주 생성 · BLEVEL 2 · 리프 2,094블록 · 통계 최신

원본 SQL

sql

SELECT COUNT(*) AS cnt, SUM(ORD_AMT * 1.1) AS amt_vat
  FROM T_ORD_BIG
 WHERE ORD_AMT * 1.1 >= :v_min

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |      1 |        |      1 |00:00:00.08 |    2104 |
|   1 |  SORT AGGREGATE       |                |      1 |      1 |      1 |00:00:00.08 |    2104 |
|*  2 |   INDEX FAST FULL SCAN| IX_ORD_BIG_AMT |      1 |  50000 |  20512 |00:00:00.07 |    2104 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_AMT"*1.1>=:V_MIN)

문제

실행계획에 인덱스가 나오는데도 2,104블록을 읽는 이유를 INDEX FAST FULL SCAN과 Predicate Information으로 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 기준 이상인 범위만 읽도록 하시오. SELECT 절의 ORD_AMT * 1.1도 고쳐야 하는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT COUNT(*) AS cnt, SUM(ORD_AMT * 1.1) AS amt_vat     -- SELECT 절의 계산은 그대로 둔다
  FROM T_ORD_BIG
 WHERE ORD_AMT >= :v_min / 1.1                            -- 계산은 바인드 쪽으로 (539,000 / 1.1 = 490,000)

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |      1 |        |      1 |00:00:00.01 |      46 |
|   1 |  SORT AGGREGATE   |                |      1 |      1 |      1 |00:00:00.01 |      46 |
|*  2 |   INDEX RANGE SCAN| IX_ORD_BIG_AMT |      1 |  20408 |  20512 |00:00:00.01 |      46 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORD_AMT">=:V_MIN/1.1)

모범 답안 서술

2번 줄은 INDEX FAST FULL SCAN이고 조건은 filter("ORD_AMT"*1.1>=:V_MIN)이다. 인덱스에는 ORD_AMT가 정렬되어 있지만 조건은 컬럼에 1.1을 곱한 값을 묻는다. 곱한 값은 인덱스에 없으므로 정렬 위에서 시작점을 정할 수 없다(access가 아니다). 옵티마이저는 COUNT와 SUM에 필요한 ORD_AMT가 인덱스에 다 있으니, 테이블 대신 더 작은 인덱스를 처음부터 끝까지 훑는 쪽을 골랐다. 실행계획에 인덱스 이름이 나왔을 뿐 하는 일은 전체 스캔이고, 100만 항목을 다 읽으며 곱해 본다(Buffers 2,104). E-Rows 50,000도 식에 대한 범위 조건의 기본 선택도 5%에서 나온 값이다. 계산을 바인드 쪽으로 옮긴다. 1.1은 양수라 양변을 나눠도 부등호 방향이 그대로이므로 ORD_AMT >= :v_min / 1.1은 원래 조건과 같은 행을 고른다(539,000 / 1.1 = 490,000). 그러면 2번 줄이 access("ORD_AMT">=:V_MIN/1.1)인 INDEX RANGE SCAN으로 바뀌어 490,000원 이상인 끝부분만 읽는다. Buffers는 2,104 → 46으로 준다. 화면 여섯 대가 1분마다 부르므로 하루 약 1,820만 블록이 약 40만 블록이 된다. SELECT 절의 SUM(ORD_AMT * 1.1)은 그대로 둔다. 인덱스 사용을 막는 것은 조건절에서 컬럼 쪽을 가공할 때뿐이고, 이미 고른 행의 값을 계산하는 것은 상관없다.

해설

곱하는 수가 음수라면 나눌 때 부등호가 뒤집힌다. 상수가 아니라 컬럼끼리 곱한 식(ORD_QTY * UNT_PRC >= :v)이라면 한쪽으로 옮길 수 없으므로 함수 기반 인덱스나 금액 컬럼을 따로 두는 모델 변경을 검토한다.
규칙이 ROUND(ORD_AMT * 1.1) >= :v처럼 반올림을 끼고 있다면 나눗셈과 정확히 같지 않다. 경계값 몇 개(489,999원 근처)로 원본과 결과를 대조한 뒤 바꾼다.
INDEX FAST FULL SCAN은 '인덱스를 탔다'는 뜻이 아니다. 멀티블록으로 인덱스 전체를 읽는 방식이므로, 실행계획에서 인덱스 이름만 보고 안심하면 안 된다(교재 02화 인덱스 스캔 6종).
기준을 낮춰 고르는 행이 30%쯤으로 많아져도 이 집계는 테이블에 가지 않으므로 RANGE SCAN이 읽는 리프는 FAST FULL보다 적다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황