인덱스 정리 뒤로 「기간 매출」이 인덱스만 읽는데도 91블록에서 4천 블록으로 늘었다

기초SELECT한빛유통64 / 700

상황

지난주 DBA가 T_ORD_BIG의 인덱스를 정리했다. 정리 기준은 이렇게 적혀 있다. "컬럼이 모두 다른 인덱스에 들어 있는 인덱스는 중복이다. (ORD_DT, ORD_AMT)의 두 컬럼은 (CUS_ID, ORD_DT, ORD_AMT)에 다 있으므로 지운다." 그 뒤로 정산 화면의 「기간 매출」(교재 02화에서 고친 그 조회, 하루 수천 번)이 느려졌다. 조민우는 "실행계획에 아직 인덱스 이름이 나오니 인덱스는 타고 있다"고 한다. 아래는 2024년 6월을 넘겼을 때다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 전부 자정 · 2024년 6월 27,390건 · CUS_ID 2,000명
남은 인덱스 IX_ORD_BIG_CUS_DT_AMT (CUS_ID, ORD_DT, ORD_AMT): ACC-043에서 「고객별 주문 원장」용으로 만든 인덱스 · BLEVEL 2 · 리프 약 4,100블록 · 통계 최신
지운 인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): 리프 3,206블록(교재 04화 실측) · 같은 조회를 91블록에 끝냈다(교재 02화 실측)
다른 인덱스는 없다 · 주문은 운영에서 계속 INSERT된다(인덱스 하나에 5만 건당 redo 약 12MB, 교재 14화)

원본 SQL

sql

SELECT COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                       |      1 |        |      1 |00:00:00.09 |    4126 |
|   1 |  SORT AGGREGATE        |                       |      1 |      1 |      1 |00:00:00.09 |    4126 |
|*  2 |   FILTER               |                       |      1 |        |  27390 |00:00:00.09 |    4126 |
|*  3 |    INDEX FAST FULL SCAN| IX_ORD_BIG_CUS_DT_AMT |      1 |  27390 |  27390 |00:00:00.08 |    4126 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

문제

실행계획에 인덱스 이름이 나오는데도 4,126블록을 읽는 이유를 INDEX FAST FULL SCAN과 인덱스의 선두 컬럼으로 설명하시오. 옵티마이저가 SKIP SCAN을 고르지 않은 이유도 설명하시오.
DBA의 정리 기준이 무엇을 놓쳤는지 설명하고, 「기간 매출」을 되살리는 DDL과 고쳐 쓴 정리 기준을 제시하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 「기간 매출」의 선두 컬럼 ORD_DT 로 시작하는 인덱스를 되살린다 (SUM 대상 ORD_AMT 까지)
CREATE INDEX IX_ORD_BIG_DT_AMT ON T_ORD_BIG (ORD_DT, ORD_AMT);

SELECT COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt          -- SQL 은 그대로
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

-- 고쳐 쓴 정리 기준
--   ① 짧은 인덱스의 컬럼이 긴 인덱스의 '앞에서부터 같은 순서' 일 때만 후보로 본다 : (A) 와 (A, B)
--   ② 후보도 바로 지우지 않는다 : INVISIBLE 로 바꾸고 V$SQL_PLAN · DBA_INDEX_USAGE 로 2주 지켜본 뒤 DROP

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

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.01 |      91 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.01 |      91 |
|*  2 |   FILTER           |                   |      1 |        |  27390 |00:00:00.01 |      91 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_DT_AMT |      1 |  27390 |  27390 |00:00:00.01 |      91 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

모범 답안 서술

3번 줄은 INDEX RANGE SCAN이 아니라 INDEX FAST FULL SCAN이고, 날짜 조건이 access가 아니라 filter다. 남은 인덱스는 CUS_ID가 선두라 항목이 고객 번호 순으로, 같은 고객 안에서 날짜 순으로 놓여 있다. 6월 주문 27,390건은 고객 2,000명의 덩어리마다 몇 건씩 흩어져 있어서, 날짜만으로는 "여기부터 여기까지"를 지목할 수 없다. 옵티마이저는 쓰는 컬럼(ORD_DT, ORD_AMT)이 다 들어 있는 이 인덱스를 테이블(5,826블록)보다 작은 덩어리로 보고 순서 없이 통째로 훑었다(4,126블록). 인덱스 이름이 보인다고 찾아간 것은 아니다. SKIP SCAN은 선두 값마다 한 번씩 찾아 들어가는 방식이다. 선두 CUS_ID가 2,000종이라 2,000번 넘게 들어가야 하므로, 옵티마이저는 멀티블록으로 한 번에 훑는 FAST FULL SCAN을 골랐다. DBA의 기준은 컬럼 집합만 보았다. 인덱스가 하는 일은 컬럼이 놓인 순서, 특히 선두 컬럼이 정한다. (ORD_DT, ORD_AMT)는 날짜 범위를 찾아가는 인덱스이고, (CUS_ID, ORD_DT, ORD_AMT)는 고객을 찾아가는 인덱스다. 하나가 다른 하나의 접두일 때만, 즉 (A)와 (A, B)처럼 앞에서부터 같은 순서일 때만 긴 쪽이 짧은 쪽의 일을 대신한다. (ORD_DT, ORD_AMT)를 다시 만들면 6월 범위만 찾아가 인덱스 안에서 합계를 낸다(Buffers 4,126 → 91). 정리 기준은 두 가지를 고친다. 첫째, 접두가 같은 인덱스만 후보로 본다. 둘째, 후보도 바로 지우지 않고 INVISIBLE로 바꿔 그 인덱스를 쓰던 SQL을 V$SQL_PLAN과 DBA_INDEX_USAGE로 지켜본 뒤 지운다. 인덱스를 되살리면 주문 INSERT마다 고칠 인덱스가 다시 하나 늘지만(5만 건당 redo 약 12MB), 하루 수천 번 4천 블록씩 더 읽는 것과는 바꿀 수 없다.

해설

함정: 선두 값이 셋뿐인 인덱스라면 IN 목록이나 SKIP SCAN으로 버틸 수 있다(ACC-020의 ORD_ST). 선두가 고객 2,000명이면 그 길이 없다. 선두 컬럼의 종류 수가 '지워도 되는가'를 가른다.
FAST FULL SCAN이 FULL보다 싸서 골라진 것이라 숫자만 보면 "FULL보다는 낫다"로 보인다. 교재 02화 §3의 ⑥(선두 컬럼 누락, 3,227블록)과 같은 모양이다.
접두가 같은 (A)와 (A, B)라도 지울 때 잃는 것이 있다. 리프 크기(교재 04화 §6), CF(ACC-022), 유일성(IDX-068)을 함께 본다.
지운 인덱스를 되살리는 동안에도 「기간 매출」은 계속 4천 블록을 읽는다. INVISIBLE은 바로 되돌릴 수 있지만, 지운 인덱스를 되살리려면 100만 행을 다시 정렬해 만드는 시간이 든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황