하루 고액 주문 목록에 filter가 보인다며 인덱스를 하나 더 만들자고 한다

기초SELECT한빛유통82 / 700

상황

리스크팀은 「하루 고액 주문」 목록을 하루 50번쯤 연다. 날짜 하나를 고르면 그날 49만 원 이상인 주문을 보여 준다. 실행계획을 보던 조민우가 이렇게 말했다. "Predicate에 filter가 있다. ORD_AMT가 access로 쓰이게 (ORD_DT, ORD_AMT) 인덱스를 하나 더 만들자." 아래는 2024년 6월 11일을 골랐을 때다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 하루 913건(전부 자정) · ORD_AMT 1만 ~ 50만 원에 고르게 분포(49만 원 이상 약 2%)
인덱스 IX_ORD_BIG_DT_ST_AMT (ORD_DT, ORD_ST, ORD_AMT): 「일자별 상태별 매출」 화면용 · BLEVEL 2 · 리프 약 4,170블록(리프 한 블록에 약 240항목) · CLUSTERING_FACTOR 약 1,000,000 · 통계 최신
다른 인덱스는 없다 · 운영에서는 주문이 계속 INSERT된다
참고(교재 04화 실측): (ORD_DT, ORD_AMT) 인덱스는 리프 3,206블록이고, 인덱스 하나를 더 두면 주문 5만 건 적재에 redo가 약 11.8MB 는다

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT  = TO_DATE(:v_dt, 'YYYYMMDD')
   AND ORD_AMT >= 490000
 ORDER BY ORD_SEQ

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 금액 분포는 어림값

xplan

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |      1 |        |     19 |00:00:00.01 |      25 |
|   1 |  SORT ORDER BY                        |                      |      1 |     18 |     19 |00:00:00.01 |      25 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG            |      1 |     18 |     19 |00:00:00.01 |      25 |
|*  3 |    INDEX RANGE SCAN                   | IX_ORD_BIG_DT_ST_AMT |      1 |     18 |     19 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_DT"=TO_DATE(:V_DT,'YYYYMMDD') AND "ORD_AMT">=490000)
       filter("ORD_AMT">=490000)

문제

3번 줄에서 어느 조건이 읽을 범위를 정하고 어느 조건이 읽으면서 거르는지 설명하시오. ORD_AMT가 filter로 쓰인 이유도 인덱스 컬럼 순서로 설명하시오.
조민우의 제안을 받아들일지 숫자로 판단하시오. 새 인덱스를 만들면 무엇이 줄고 무엇이 그대로인지, 그 대가는 무엇인지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고치지 않는다. 인덱스도 SQL 도 그대로 둔다 (access 범위가 하루치 913 항목뿐이다)
SELECT ORD_SEQ, CUS_ID, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT  = TO_DATE(:v_dt, 'YYYYMMDD')
   AND ORD_AMT >= 490000
 ORDER BY ORD_SEQ;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 금액 분포는 어림값

xplan

[조민우 안을 INVISIBLE 로 만들어 세션에서만 재 본 결과 — IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |     19 |00:00:00.01 |      22 |
|   1 |  SORT ORDER BY                        |                   |      1 |     18 |     19 |00:00:00.01 |      22 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG         |      1 |     18 |     19 |00:00:00.01 |      22 |
|*  3 |    INDEX RANGE SCAN                   | IX_ORD_BIG_DT_AMT |      1 |     18 |     19 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT"=TO_DATE(:V_DT,'YYYYMMDD') AND "ORD_AMT">=490000)

→ 인덱스 6 → 3 · 전체 25 → 22 · 하루 50번이면 하루 150블록을 아낀다 (테이블 19 는 그대로)

모범 답안 서술

3번 줄에서 읽을 범위를 정하는 것은 "ORD_DT"=TO_DATE(:V_DT,'YYYYMMDD')이다. "ORD_AMT">=490000은 access에도 적혀 있지만 filter에 또 나온다. 인덱스는 ORD_DT → ORD_ST → ORD_AMT 순으로 정렬되어 있는데, 가운데 컬럼 ORD_ST에 조건이 없어서 ORD_AMT는 범위를 줄이지 못하고 읽으면서 거를 뿐이다. 그래서 그날의 913항목(리프 4블록 안팎, Buffers 6)을 다 읽고 49만 원 이상인 19건을 남긴다. filter가 보인다고 다 고칠 일은 아니다. access가 정한 범위가 하루치뿐이라, 거르면서 버리는 것은 894항목, 리프 몇 블록이다. 조민우 안 (ORD_DT, ORD_AMT)로 바꿔 재 보면 인덱스는 6 → 3, 전체는 25 → 22블록이다. 하루 50번이면 하루 150블록을 아낀다. 테이블 랜덤 액세스 19번은 그대로다. 인덱스 filter는 테이블에 가기 전에 거르므로, 지금도 테이블에는 조건을 만족하는 19건만 찾아간다. 남은 일량의 대부분은 이 19번이고, 어느 인덱스를 써도 달라지지 않는다. 대가는 크다. 100만 행에 리프 3,206블록짜리 인덱스가 하나 늘고, 주문을 INSERT할 때마다 인덱스 하나를 더 고친다(교재 04화 실측: 5만 건에 redo 약 11.8MB). 하루 150블록을 아끼려고 그 비용을 치를 이유가 없으므로 새 인덱스는 만들지 않고 SQL도 그대로 둔다.

해설

filter를 봤으면 먼저 access 범위가 얼마나 넓은지 본다. 이 SQL은 ORD_DT = 하나로 하루치에서 시작하고 끝난다. ACC-001처럼 석 달 치를 읽고 버리는 경우와는 다르다.
인덱스 filter와 테이블 필터는 다르다. 인덱스 줄의 filter는 테이블에 가기 전에 거르지만, TABLE ACCESS 줄의 filter는 테이블 블록을 찾아간 뒤에 거른다. 이 문제의 filter는 앞의 것이라 값싸다.
함정: 가운데 ORD_ST를 ORD_ST IN ('COMPLETE', 'PENDING', 'CANCEL')로 채우면 ORD_AMT도 access가 된다. 하지만 상태마다 인덱스를 다시 찾아 내려가므로 3번 × 3블록으로 오히려 늘고, 상태값이 늘면 그 주문이 조용히 빠진다.
기간을 고르는 화면(예: 한 달 고액 주문)으로 바뀌면 판단이 달라진다. 한 달이면 27,390항목, 리프 약 115블록을 읽는다. 그때는 범위 조건이 둘인 문제가 된다(ACC-017).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 금액 분포는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 금액 분포는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황