시간대별 메뉴 판매 리포트를 위해 함수 기반 인덱스를 만들었는데 실행계획이 그대로다

중급DDL버거134 / 700

상황

버거원 상품기획팀의 「시간대별 메뉴 판매」는 기간(보통 한 달)을 고르면 시간대와 메뉴별 판매 수량을 표로 보여 준다. 하루 수십 번 열고, 한 달을 고르면 2,700블록 가까이 읽는다. 조민우는 "GROUP BY에 SUBSTR(ORD_HMS, 1, 2)가 있으니 그 식으로 인덱스를 만들면 된다"며 지난주 함수 기반 인덱스를 만들었다. 그런데 실행계획은 그대로이고, 운영팀은 POS 주문상품 INSERT가 고치는 인덱스가 하나 늘었다고 알려 왔다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · 주문 시각 순으로 쌓였다 · 한 달 약 103,000행 · POS가 하루 약 3,400행 INSERT
인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 9,200블록 · CLUSTERING_FACTOR 약 55,100 · 「일별 주문 조회」도 쓴다
지난주 만든 ORD_ITEM_FX01 (SUBSTR(ORD_HMS, 1, 2), ITEM_ID): 리프 약 5,700블록 · 통계 최신
교재 14화 실측 비율로는 인덱스 하나가 5만 건 적재에 redo 약 11.8MB를 더한다 · POS 하루 3,400행이면 하루 약 0.8MB다

원본 SQL

sql

SELECT SUBSTR(ORD_HMS, 1, 2) AS hh, ITEM_ID, SUM(ORD_ITEM_QTY) AS qty
  FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY SUBSTR(ORD_HMS, 1, 2), ITEM_ID;

-- 지난주 조민우
CREATE INDEX ORD_ITEM_FX01 ON ORD_ITEM (SUBSTR(ORD_HMS, 1, 2), ITEM_ID);

실행계획 학습용 예시 · 규모는 schemas 그대로 · 리프 수는 어림값

xplan

[2013년 11월 · FX01을 만든 뒤에도 같다]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |   1288 |00:00:00.09 |    2690 |
|   1 |  HASH GROUP BY                       |              |      1 |   1288 |   1288 |00:00:00.09 |    2690 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |    98K |   103K |00:00:00.06 |    2690 |
|*  3 |    INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |    98K |   103K |00:00:00.02 |     387 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

문제

ORD_ITEM_FX01이 이 리포트에 쓰이지 않는 이유를 함수 기반 인덱스가 어떤 조건에 쓰이는지로 설명하시오.
이 리포트가 테이블을 읽지 않도록 인덱스를 고치시오. ORD_ITEM_FX01을 어떻게 할지와, POS INSERT가 고치는 인덱스 수가 어떻게 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 쓰이지 않는 함수 기반 인덱스를 지운다 (POS INSERT 가 고치는 인덱스가 다시 둘)
DROP INDEX ORD_ITEM_FX01;

-- X01 을 리포트가 쓰는 컬럼까지 넓힌 인덱스로 바꾼다 : 앞의 (ORD_DT, ORD_HMS) 가 같아 일별 주문 조회도 그대로 쓴다
CREATE INDEX ORD_ITEM_X03 ON ORD_ITEM (ORD_DT, ORD_HMS, ITEM_ID, ORD_ITEM_QTY) INVISIBLE ONLINE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 리포트와 일별 주문 조회를 둘 다 잰 뒤
ALTER INDEX ORD_ITEM_X03 VISIBLE;
DROP INDEX ORD_ITEM_X01;

SELECT SUBSTR(ORD_HMS, 1, 2) AS hh, ITEM_ID, SUM(ORD_ITEM_QTY) AS qty     -- SQL 은 그대로
  FROM ORD_ITEM
 WHERE ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY SUBSTR(ORD_HMS, 1, 2), ITEM_ID;

개선 후 실행계획 학습용 예시 · 규모는 schemas 그대로 · 리프 수는 어림값

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |   1288 |00:00:00.04 |     484 |
|   1 |  HASH GROUP BY                       |              |      1 |   1288 |   1288 |00:00:00.04 |     484 |
|*  2 |   INDEX RANGE SCAN                   | ORD_ITEM_X03 |      1 |    98K |   103K |00:00:00.02 |     484 |
---------------------------------------------------------------------------------------------------------------
   2 - access("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

모범 답안 서술

함수 기반 인덱스는 조건절(WHERE)에 그 식이 나와 범위를 좁힐 때(access) 쓰인다. 이 리포트의 조건은 ORD_DT 범위 하나인데, ORD_ITEM_FX01에는 ORD_DT가 없다. SUBSTR(ORD_HMS, 1, 2)는 GROUP BY에만 있고 조건이 아니므로 FX01로는 한 달을 골라낼 수 없다. 리포트가 쓰는 ORD_DT와 ORD_ITEM_QTY가 없으니 FX01만 읽고 끝낼 수도 없다. 그래서 옵티마이저는 여전히 X01로 한 달 103,000행을 찾고 테이블을 약 2,300블록 읽는다(합계 2,690블록). GROUP BY와 SELECT의 식은 그 식에 쓰인 컬럼만 있으면 계산된다. SUBSTR(ORD_HMS, 1, 2)는 ORD_HMS가 인덱스에 있으면 인덱스에서 바로 계산되므로 식으로 인덱스를 만들 필요가 없다. 리포트에 필요한 것은 ORD_DT로 범위를 좁히고 ORD_HMS, ITEM_ID, ORD_ITEM_QTY를 테이블에 가지 않고 읽는 인덱스다. 그래서 FX01은 지우고, X01을 (ORD_DT, ORD_HMS, ITEM_ID, ORD_ITEM_QTY)로 넓힌 인덱스로 바꾼다. 앞의 두 컬럼이 X01과 같으므로 「일별 주문 조회」도 그대로 쓴다. 리포트는 한 달 치 리프 약 480블록만 읽고 테이블에 가지 않는다(2,690 → 약 484). POS INSERT가 고치는 인덱스는 FX01 때문에 셋이 됐다가, FX01을 지우고 X01을 바꾸면 다시 둘(PK와 새 인덱스)이다. 대가는 X01 자리의 인덱스가 넓어진 만큼(리프 약 9,200 → 11,600블록) 커지는 것이다. 바꾸는 동안 두 인덱스가 함께 있으므로 INVISIBLE로 만들어 재고, 확인한 뒤 옛 X01을 지운다.

해설

함정: GROUP BY나 ORDER BY에 나오는 식을 보고 함수 기반 인덱스를 만드는 일이 흔하다. 함수 기반 인덱스가 필요한 것은 조건절에서 컬럼이 식으로 감싸여 범위를 좁히지 못할 때다(IDX 단원).
조건이 SUBSTR(ORD_HMS, 1, 2) = '12'처럼 식이었다면 함수 기반 인덱스를 생각할 수 있다. 그래도 ORD_HMS BETWEEN '120000' AND '125959'로 바꾸면 일반 인덱스로 받는다.
함수 기반 인덱스도 인덱스 하나만큼 DML 비용이 든다. 만든 뒤에는 그 인덱스를 쓰는 SQL이 실제로 있는지 실행계획으로 확인한다(교재 14화의 적재 청구서).
1년을 자주 고른다면 시간대와 메뉴별 일 집계(하루 1,288행)를 밤마다 만들어 두는 편이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 규모는 schemas 그대로 · 리프 수는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 규모는 schemas 그대로 · 리프 수는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황