같은 취소 주문 2만 건인데 남은 인덱스로 읽자 마감 점검의 테이블 블록이 5,826에서 2만으로 늘었다

실전SELECT한빛유통97 / 700

상황

월말 마감의 「취소 주문 전수 점검」은 취소 주문 전부를 고객별로 묶어 원장과 맞춘다. 마감 날에는 단계마다 돌아서 하루 30번쯤 실행된다. 소스에는 /*+ INDEX(o (ORD_ST)) */ 힌트와 함께 "상태 인덱스를 꼭 타게 (2019 강도현)"라는 주석이 있다. 지난주 DBA가 접두 중복 규칙(교재 04화 §6)에 따라 IX_ORD_BIG_ST (ORD_ST)를 지웠다. "(ORD_ST, ORD_DT)의 접두부라서 하던 일은 그 인덱스가 대신한다." 실행계획을 보니 점검 SQL도 남은 인덱스를 탄다. 그런데 이번 마감에서 한 번에 5,878블록이던 점검이 20,074블록이 됐다. 박팀장은 이렇게 주문했다. "인덱스를 되살릴지, 다른 길이 있는지, 정리 규칙은 어떻게 고칠지 숫자로 적어 와라."

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록(블록당 약 172행) · ORD_SEQ 순으로 쌓였다 · ORD_DT 1,095일(하루 913건, 전부 자정)
취소(ORD_ST = 'CANCEL') 20,000건(2%): ORD_SEQ가 50의 배수인 주문이다 · 같은 날짜의 취소 주문은 ORD_SEQ가 10,950씩 떨어져 있다 · 취소 주문이 있는 고객은 40명
지운 인덱스 IX_ORD_BIG_ST (ORD_ST): BLEVEL 2 · 리프 약 2,500블록 · CLUSTERING_FACTOR 17,478
남은 인덱스 IX_ORD_BIG_ST_DT (ORD_ST, ORD_DT): 「취소 추이」 화면용(ACC-001의 처방) · BLEVEL 2 · 리프 약 3,610블록 · CLUSTERING_FACTOR 약 1,000,000
그 밖에 IX_ORD_BIG_DT (ORD_DT) · 통계 최신 · ORD_ST에 히스토그램 · 주문은 운영에서 계속 INSERT된다(교재 04화 실측: 인덱스 하나에 5만 건 적재 redo 약 11.8MB)

원본 SQL

sql

SELECT /*+ INDEX(o (ORD_ST)) */
       o.CUS_ID, COUNT(*) AS cnl_cnt, SUM(o.ORD_AMT) AS cnl_amt
  FROM T_ORD_BIG o
 WHERE o.ORD_ST = 'CANCEL'
 GROUP BY o.CUS_ID

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[지난달 마감: IX_ORD_BIG_ST가 있을 때]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |     40 |00:00:00.06 |    5878 |
|   1 |  HASH GROUP BY                       |                  |      1 |   2000 |     40 |00:00:00.06 |    5878 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG        |      1 |  20000 |  20000 |00:00:00.05 |    5878 |
|*  3 |    INDEX RANGE SCAN                  | IX_ORD_BIG_ST    |      1 |  20000 |  20000 |00:00:00.01 |      52 |
------------------------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_ST"='CANCEL')

[이번 마감: IX_ORD_BIG_ST를 지운 뒤]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |     40 |00:00:00.18 |   20074 |
|   1 |  HASH GROUP BY                       |                  |      1 |   2000 |     40 |00:00:00.18 |   20074 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG        |      1 |  20000 |  20000 |00:00:00.17 |   20074 |
|*  3 |    INDEX RANGE SCAN                  | IX_ORD_BIG_ST_DT |      1 |  20000 |  20000 |00:00:00.01 |      74 |
------------------------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_ST"='CANCEL')

[같은 조건 · FULL 강제]  TABLE ACCESS FULL 5,826

문제

같은 20,000행을 같은 모양(INDEX RANGE SCAN 다음 TABLE ACCESS BY INDEX ROWID)으로 읽는데 테이블 쪽 Buffers가 약 5,826과 20,000으로 갈리는 이유를, 두 인덱스 안에서 'CANCEL' 항목이 어떤 순서로 놓여 있는지를 근거로 설명하시오.
처방을 셋 이상 들어 Buffers와 대가를 비교하시오. 지운 인덱스를 되살리는 안도 비교에 넣고, 하나를 골라 SQL과 DDL을 작성하시오.
"접두부 인덱스는 지워도 된다"는 정리 규칙에 무엇을 더해야 하는지, 이 SQL의 힌트 모양과 함께 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① (고른 것) 강제 힌트를 뗀다 — 취소 주문은 모든 블록에 있다 · 어느 인덱스 경로도 FULL 5,826 을 넘지 못한다
SELECT o.CUS_ID, COUNT(*) AS cnl_cnt, SUM(o.ORD_AMT) AS cnl_amt
  FROM T_ORD_BIG o
 WHERE o.ORD_ST = 'CANCEL'
 GROUP BY o.CUS_ID;

-- ② (비교) IX_ORD_BIG_ST 되살리기 : 5,878 — FULL 과 같고 인덱스 하나만큼 적재 비용이 든다
-- ③ (비교) 커버링 : 약 88 — 마감 날 30번 × 5,826 을 아끼려고 1년 내내 주문 INSERT 마다 인덱스를 고친다
--    CREATE INDEX IX_ORD_BIG_ST_CUS_AMT ON T_ORD_BIG (ORD_ST, CUS_ID, ORD_AMT) INVISIBLE;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     40 |00:00:00.05 |    5826 |
|   1 |  HASH GROUP BY     |           |      1 |   2000 |     40 |00:00:00.05 |    5826 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |  20000 |  20000 |00:00:00.04 |    5826 |
----------------------------------------------------------------------------------------
   2 - filter("O"."ORD_ST"='CANCEL')

[비교]  ② IX_ORD_BIG_ST 되살리기 5,878 · ③ 커버링 (ORD_ST, CUS_ID, ORD_AMT) INDEX RANGE SCAN 약 88

모범 답안 서술

두 계획의 3번 줄(인덱스)은 52와 74로 비슷하다. 갈린 것은 2번 줄 테이블 액세스다. 비유일 인덱스에서 키 값이 같은 항목은 ROWID 순, 곧 테이블에 저장된 순서대로 놓인다. 지운 (ORD_ST) 인덱스의 'CANCEL' 구간은 ROWID 순이라서 테이블 블록을 앞에서부터 차례로 한 번씩 읽었다. 한 블록 안의 취소 주문 서너 건은 연달아 읽히므로 블록 하나에 한 번만 센다. 그래서 테이블 쪽이 5,826블록이었다. 남은 (ORD_ST, ORD_DT) 인덱스의 'CANCEL' 구간은 날짜 순이다. 같은 날짜의 취소 주문은 ORD_SEQ가 10,950씩 떨어져 있어서 항목마다 다른 블록을 가리킨다. 그래서 2만 항목이 그대로 2만 번의 블록 방문이 됐다(Buffers 5,878 → 20,074). 같은 컬럼으로 시작해도 인덱스마다 CF가 다르다(17,478 대 약 1,000,000). 처방은 셋을 비교했다. ① 힌트를 뗀다: 취소 주문은 50건마다 하나라 5,826블록 전부에 서너 건씩 있다. 가장 잘 읽어도 모든 블록을 한 번씩 읽어야 하므로 어떤 인덱스 경로도 FULL(5,826)보다 싸질 수 없다. 힌트를 떼면 옵티마이저가 FULL을 고른다(20,074 → 5,826). ② 지운 인덱스를 되살린다: 5,878로 FULL과 같다. 얻는 것이 없는데 인덱스 하나만큼의 적재 비용(5만 건에 redo 약 11.8MB)을 계속 낸다. ③ 커버링 (ORD_ST, CUS_ID, ORD_AMT): 'CANCEL' 구간 리프 약 86블록만 읽으므로 약 88이다. 하지만 마감 날 30번 × 5,826블록을 아끼려고 1년 내내 주문 INSERT마다 인덱스를 하나 더 고치게 된다. 그래서 ①을 고른다. 마감이 아니라 하루 수천 번 도는 화면이었다면 ③이 맞다. 힌트 INDEX(o (ORD_ST))는 인덱스 이름이 아니라 컬럼을 적은 모양이라서, ORD_ST로 시작하는 인덱스 가운데 하나를 쓰라는 뜻이다. (ORD_ST)가 사라지자 남은 (ORD_ST, ORD_DT)를 골랐고, 옵티마이저 혼자였다면 고르지 않았을 길(비용 약 2만)로 들어갔다. 정리 규칙에는 다음을 더한다. 접두부 인덱스를 지우기 전에 그 인덱스를 쓰던 SQL을 V$SQL_PLAN과 DBA_INDEX_USAGE로 찾고, 먼저 INVISIBLE로 바꿔 그 SQL들을 남는 인덱스로 재 본다. 선두 컬럼이 같아도 CF가 다르면 테이블 액세스 비용이 다르다. 그 SQL에 인덱스를 고정하는 힌트가 있는지도 함께 본다.

해설

함정: "선두 컬럼이 같으니 같은 일을 한다"는 인덱스 쪽만 본 말이다. 인덱스 쪽 Buffers는 52 → 74로 거의 같았고, 차이는 전부 테이블 쪽에서 났다. 인덱스 뒤에 컬럼을 붙이면 같은 키 안의 순서가 ROWID 순에서 그 컬럼 순으로 바뀌어 CF가 나빠질 수 있다.
2%만 읽는데 FULL이 맞는 이유는 '몇 %를 읽는가'가 아니라 '그 행이 몇 블록에 퍼져 있는가'다. 취소 주문 20,000건이 5,826블록 전부에 있으니 블록 기준으로는 100%를 읽는다.
교재 04화 §6의 접두 중복 표에도 "지울 때 잃을 수 있는 것"이 있다. 그 표의 예는 리프 크기였고, 이 문제는 CF다. 지우는 일은 만드는 일보다 되돌리기 어려우므로 INVISIBLE로 먼저 확인한다.
컬럼으로 적은 힌트는 인덱스 이름이 바뀌어도 살아남는 장점이 있지만, 이번처럼 뜻하지 않은 인덱스를 고를 수도 있다. 힌트 대장(교재 06화)에 그 힌트가 전제하는 인덱스를 적어 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황