인덱스를 새로 만들었는데 취소 추이 그래프는 여전히 무겁다

기초DDL한빛유통76 / 700

상황

정산 대시보드의 「취소 추이」는 기간을 고르면 일자별 취소 건수를 그래프로 그린다. 보통 한 분기를 고르며, 하루에 수천 번 조회된다. 지난달 조민우가 인덱스를 하나 만들면서 이런 메모를 남겼다. "종류가 많은 컬럼을 앞에 둔다. ORD_DT 1,095종, ORD_ST 3종." 인덱스는 타는데 생각만큼 빨라지지 않았다고 한다. 아래는 2024년 2분기(4 ~ 6월)를 골랐을 때다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 1,095일(하루 913건, 전부 자정) · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2%
2024년 2분기: 83,083건 · 그중 CANCEL 1,644건(취소가 있는 날은 18일)
인덱스 IX_ORD_BIG_DT_ST (ORD_DT, ORD_ST): BLEVEL 2 · 리프 약 3,890블록 · 통계 최신 · ORD_ST에 히스토그램

원본 SQL

sql

SELECT ORD_DT, COUNT(*) AS cancel_cnt
  FROM T_ORD_BIG
 WHERE ORD_ST = 'CANCEL'
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY ORD_DT
 ORDER BY ORD_DT

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

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |     18 |00:00:00.02 |     326 |
|   1 |  SORT GROUP BY NOSORT |                  |      1 |     91 |     18 |00:00:00.02 |     326 |
|*  2 |   FILTER              |                  |      1 |        |   1644 |00:00:00.02 |     326 |
|*  3 |    INDEX RANGE SCAN   | IX_ORD_BIG_DT_ST |      1 |   1662 |   1644 |00:00:00.02 |     326 |
---------------------------------------------------------------------------------------------------

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

문제

인덱스를 타는데도 326블록을 읽는 이유를 Predicate Information의 3번 줄(access와 filter에 ORD_ST가 둘 다 있다)을 근거로 설명하시오.
인덱스를 다시 설계하고 DDL을 작성하시오. 조민우의 메모가 왜 틀렸는지도 한 문장으로 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- = 로 들어오는 ORD_ST 를 앞에, 범위인 ORD_DT 를 뒤에 — 'CANCEL 의 그 석 달' 이 한 구간이 된다
CREATE INDEX IX_ORD_BIG_ST_DT ON T_ORD_BIG (ORD_ST, ORD_DT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 내 세션에서만 재고 → T2 → VISIBLE
-- 기존 IX_ORD_BIG_DT_ST 는 다른 SQL 이 쓰는지 확인한 뒤에 INVISIBLE → DROP

SELECT ORD_DT, COUNT(*) AS cancel_cnt          -- SQL 은 그대로
  FROM T_ORD_BIG
 WHERE ORD_ST = 'CANCEL'
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 GROUP BY ORD_DT
 ORDER BY ORD_DT;

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

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |     18 |00:00:00.01 |       9 |
|   1 |  SORT GROUP BY NOSORT |                  |      1 |     91 |     18 |00:00:00.01 |       9 |
|*  2 |   FILTER              |                  |      1 |        |   1644 |00:00:00.01 |       9 |
|*  3 |    INDEX RANGE SCAN   | IX_ORD_BIG_ST_DT |      1 |   1662 |   1644 |00:00:00.01 |       9 |
---------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_ST"='CANCEL' AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND
              "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

모범 답안 서술

3번 줄의 access에 "ORD_ST"='CANCEL'이 있지만 filter에도 같은 조건이 또 있다. 인덱스가 ORD_DT로 먼저 정렬되어 있어서 석 달 안의 CANCEL 항목은 날짜마다 흩어져 있다. 범위 조건인 ORD_DT 뒤에 오는 ORD_ST는 읽기 시작점만 좁힐 뿐 읽을 구간을 줄이지 못하고, filter로 거르는 데 그친다. 그래서 석 달 치 83,083항목(리프 약 320블록)을 전부 읽고 CANCEL 1,644건만 남긴다(Buffers 326). =로 들어오는 ORD_ST를 앞에, 범위 조건인 ORD_DT를 뒤에 두어 (ORD_ST, ORD_DT)로 만들면 'CANCEL이면서 4 ~ 6월'인 항목이 인덱스 안에서 연속된 한 구간이 된다. access만 남고 filter가 사라진다(Buffers 326 → 9). 구간 안은 여전히 ORD_DT 순이므로 SORT GROUP BY NOSORT도 그대로 유지된다. 조민우의 메모는 '종류가 많은 컬럼을 앞에'였다. 하지만 컬럼 순서를 정하는 첫 기준은 종류 수(카디널리티)가 아니라 조건의 모양이다. 늘 =로 들어오는 컬럼을 먼저 두고, 범위 조건 컬럼은 뒤에 둔다. 새 인덱스는 INVISIBLE로 만들어 세션에서만 재 보고, 기존 (ORD_DT, ORD_ST)를 다른 SQL이 쓰는지(V$SQL_PLAN, DBA_INDEX_USAGE) 확인한 뒤 교체한다. 둘 다 두면 주문을 INSERT할 때마다 인덱스 둘을 고쳐야 하므로 적재 비용이 든다(교재 04화 실측: 5만 건에 인덱스 하나당 redo 약 11.8MB).

해설

범위가 짧으면 차이가 작다. 하루치라면 (ORD_DT, ORD_ST)로도 913항목(리프 서너 블록)만 읽는다. 대시보드가 석 달이나 1년을 고르는 화면이라서 순서가 중요해진다.
기존 인덱스를 바꿀 수 없는 사정이라면, 기간을 날짜 IN 목록으로 풀어 ORD_DT = 각 날짜로 만드는 방법도 있다(INLIST ITERATOR가 되어 뒤 컬럼도 access가 된다). 다만 석 달이면 값이 91개라 SQL이 지저분해진다.
이 데이터는 적재 스크립트가 MOD로 만들었기 때문에 CANCEL이 닷새에 하루만 있다(석 달 중 18일). 실데이터라면 날마다 고르게 있겠지만 결론은 같다.
교재 04화 §6에서 든 컬럼 순서의 기준은 넷이다(늘 =로 들어오는 컬럼, 늘 나오는 컬럼, 선택도, 정렬). 선택도는 그중 세 번째다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황