인덱스를 정리한 다음 주, 분기 마감 상황판이 인덱스를 통째로 읽는다

모범 답안 SQL ​

-- 전제를 먼저 확인하고 제약으로 선언한다 (값이 셋뿐이고 NULL 이 없다는 것을 DB 가 보장한다)
SELECT COUNT(*) FROM T_ORD_BIG
 WHERE ORD_ST IS NULL OR ORD_ST NOT IN ('COMPLETE', 'PENDING', 'CANCEL');   -- 0 이어야 한다
ALTER TABLE T_ORD_BIG MODIFY (ORD_ST NOT NULL);
ALTER TABLE T_ORD_BIG ADD CONSTRAINT CK_ORD_BIG_ST CHECK (ORD_ST IN ('COMPLETE', 'PENDING', 'CANCEL'));

-- 선두 ORD_ST 를 IN 목록으로 채운다 — 상태마다 (ORD_ST = , ORD_DT 범위) 로 찾는다
SELECT ORD_DT, ORD_ST, COUNT(*) AS ord_cnt
  FROM T_ORD_BIG
 WHERE ORD_ST IN ('COMPLETE', 'PENDING', 'CANCEL')
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY ORD_DT, ORD_ST
 ORDER BY ORD_DT, ORD_ST;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 · 인덱스 규모는 ACC-001과 같다 ​

------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |    127 |00:00:00.03 |     330 |
|   1 |  SORT GROUP BY        |                  |      1 |    182 |    127 |00:00:00.03 |     330 |
|*  2 |   FILTER              |                  |      1 |        |  83083 |00:00:00.02 |     330 |
|   3 |    INLIST ITERATOR    |                  |      1 |        |  83083 |00:00:00.02 |     330 |
|*  4 |     INDEX RANGE SCAN  | IX_ORD_BIG_ST_DT |      3 |  83083 |  83083 |00:00:00.02 |     330 |
------------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   4 - access((("ORD_ST"='CANCEL' OR "ORD_ST"='COMPLETE' OR "ORD_ST"='PENDING')) AND
              "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

[상태별로 읽는 양] COMPLETE 74,866항목 리프 약 291 · PENDING 6,573 약 26 · CANCEL 1,644 약 7 · 찾아 내려가기 3번 × 2

모범 답안 서술 ​

옛 인덱스 (ORD_DT, ORD_ST)는 ORD_DT가 선두라서 이 SQL의 ORD_DT 범위가 그대로 access가 되어 분기 치 83,083항목(326블록)만 읽었다. 새 인덱스 (ORD_ST, ORD_DT)는 ORD_ST로 먼저 정렬되어 있다. 이 SQL에는 ORD_ST 조건이 없으므로 선두 조건이 빠져 범위를 지목할 수 없다. 그래서 옵티마이저는 인덱스 전체를 멀티블록으로 훑는 INDEX FAST FULL SCAN(3,904블록)을 골랐다. 테이블(5,826블록)보다는 작지만 옛 인덱스의 12배다. 관찰에서 놓친 이유는 관찰 기간에 있다. 이 상황판은 분기가 끝난 다음 주에만 띄우는데, 화요일부터 금요일까지 나흘 동안만 V$SQL_PLAN을 봤다. 관찰 기간이 분기 마감 주간을 덮지 못했다. 월말이나 분기 마감처럼 주기가 긴 SQL은 그 주기를 덮을 만큼 관찰하거나, 소스와 AWR 이력(DBA_HIST_SQL_PLAN)을 함께 찾아야 한다. 대안을 비교한다. ① IN 목록: ORD_ST IN ('COMPLETE', 'PENDING', 'CANCEL')로 선두를 채우면 상태마다 ORD_ST = 값 AND ORD_DT 범위로 찾는다(INLIST ITERATOR, Starts 3). 읽는 항목은 옛 인덱스와 같은 83,083개라 330블록이다. 위험은 결과다. ORD_ST가 NULL인 주문이나 나중에 생길 새 상태(예: REFUND)는 목록에 없어서 리포트에서 조용히 빠진다. ② SKIP SCAN: INDEX_SS 힌트를 주거나 옵티마이저가 고르게 두면 선두 값마다 건너뛰며 읽어 330블록 안팎이다. 인덱스에 들어 있는 값을 차례로 찾아가므로 새 상태가 생겨도 빠지지 않는다. 대신 힌트에 기대거나 옵티마이저의 선택을 믿어야 하고, 선두 값의 종류가 늘면 그만큼 느려진다. ③ 옛 인덱스를 다시 만든다: 326블록이지만 인덱스가 다시 둘이 되어, 주문 INSERT마다 인덱스 하나를 더 고치는 적재 비용이 든다(교재 04화: 5만 건에 인덱스 하나당 redo 약 11.8MB). ④ 그대로 둔다: 3,904 × 하루 290번 ≈ 113만 블록이다. 일주일만 띄우는 화면이라 견딜 수는 있지만, 인덱스를 바꾼 목적(일을 줄이는 것)에 어긋난다. ①을 고르되 전제를 제약으로 선언한다. 먼저 NULL이나 다른 값이 0건인지 세고, ORD_ST에 NOT NULL과 CHECK 제약을 건다. 그러면 새 상태를 넣는 프로그램은 리포트에서 조용히 빠지는 대신 제약 위반으로 바로 드러나고, 상태를 늘리는 사람은 제약과 이 리포트를 함께 고치게 된다. 제약을 거는 순간 100만 행을 한 번 읽어 검증하고 그동안 DML을 막으므로 업무 시간을 피한다. Buffers는 3,904 → 330이다.

해설 ​

• 컬럼 순서를 바꾸는 일은 그 인덱스를 쓰던 모든 SQL의 access 조건을 바꾸는 일이다. ACC-001에서 '기존 인덱스를 쓰는 다른 SQL을 확인하라'고 한 이유가 이 문제다. 확인은 관찰 기간이 그 SQL의 주기를 덮어야 의미가 있다.
• 다른 정답: ②를 골라도 된다. 결과가 틀릴 위험이 없다는 점에서는 ②가 낫다. 다만 INDEX_SS 힌트를 힌트 대장에 적어 두고, ORD_ST의 종류가 늘면 다시 잰다.
• IN 목록으로 빠진 선두를 채우는 방법은 BETWEEN을 IN 목록으로 바꾸는 방법과 원리가 같다. 앞 컬럼을 =로 만들어 뒤 컬럼을 access로 쓰게 한다. 효과를 보려면 값이 몇 개 안 되고, 그 값 목록이 닫혀 있어야 한다.
• 옛 인덱스를 지우기 전에 INVISIBLE로 두었다면, 월요일 아침에 VISIBLE로 되돌리는 것으로 바로 복구할 수 있었다. 지운 인덱스는 다시 만드는 시간이 통째로 든다(교재 04화 §6, §8).

관련 교재 ​

• 04화 · 결합 인덱스 컬럼 순서
• 04화 · INVISIBLE 검증 절차
• 02화 · 인덱스 스캔 6종