클레임 주문 한 건을 찾으려고 만든 인덱스가 기존 인덱스보다 세 배를 읽는다

기초DDL버거83 / 700

상황

버거원 고객센터는 "SH0123 매장에서 11월 2일 저녁 6시쯤 주문했는데 음식이 잘못 나왔다" 같은 클레임을 받으면, 매장과 날짜, 시각 앞뒤 30분으로 주문을 찾는다. 하루에 수백 번 조회한다. 조민우가 이 조회를 위해 인덱스를 설계했다. 메모에는 "선택도가 좋은 순서로: ORD_HMS 50,400종, ORD_DT 730종, SHOP_NO 200종"이라고 적었다. INVISIBLE로 만들어 세션에서 재 보니 기존 인덱스보다 세 배를 읽었다.

테이블과 인덱스

ORD_RANDOM(운영본): 1,000,000행 · 22,414블록 · 무작위 순서로 적재
ORD_DT VARCHAR2(8) 2012 ~ 2013(730일) · ORD_HMS VARCHAR2(6) '090000' ~ '225959'에 고르게 분포(한 시간에 약 7.1%) · SHOP_NO 200개 매장 · 매장 하나의 하루 주문은 평균 6.8건
인덱스 ORD_RANDOM_PK (ORD_NO) · ORD_RANDOM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 3,774 · CLUSTERING_FACTOR 999,958
조민우 안 ORD_RANDOM_X09 (ORD_HMS, ORD_DT, SHOP_NO): INVISIBLE · BLEVEL 2 · 리프 약 4,350블록(리프 한 블록에 약 230항목)

원본 SQL

sql

SELECT ORD_NO, ORD_HMS
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop
   AND ORD_DT  = :v_dt
   AND ORD_HMS BETWEEN :v_hms_from AND :v_hms_to
 ORDER BY ORD_HMS;

-- 조민우 안
CREATE INDEX ORD_RANDOM_X09 ON ORD_RANDOM (ORD_HMS, ORD_DT, SHOP_NO) INVISIBLE;

실행계획 학습용 예시 · 인덱스 규모는 교재 04화 실측

xplan

[지금 — ORD_RANDOM_X01 · SH0123 · '20131102' · '173000' ~ '183000']
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |      1 |00:00:00.01 |     100 |
|   1 |  SORT ORDER BY                        |                |      1 |      1 |      1 |00:00:00.01 |     100 |
|*  2 |   FILTER                              |                |      1 |        |      1 |00:00:00.01 |     100 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |      1 |      1 |00:00:00.01 |     100 |
|*  4 |     INDEX RANGE SCAN                  | ORD_RANDOM_X01 |      1 |     98 |     99 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------
   2 - filter(:V_HMS_FROM<=:V_HMS_TO)
   3 - filter("SHOP_NO"=:V_SHOP)
   4 - access("ORD_DT"=:V_DT AND "ORD_HMS">=:V_HMS_FROM AND "ORD_HMS"<=:V_HMS_TO)

[조민우 안 — ORD_RANDOM_X09 · 세션에서만 VISIBLE · 같은 값]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |      1 |00:00:00.02 |     314 |
|*  1 |  FILTER                               |                |      1 |        |      1 |00:00:00.02 |     314 |
|   2 |   TABLE ACCESS BY INDEX ROWID         | ORD_RANDOM     |      1 |      1 |      1 |00:00:00.02 |     314 |
|*  3 |    INDEX RANGE SCAN                   | ORD_RANDOM_X09 |      1 |      1 |      1 |00:00:00.02 |     313 |
-----------------------------------------------------------------------------------------------------------------
   1 - filter(:V_HMS_FROM<=:V_HMS_TO)
   3 - access("ORD_HMS">=:V_HMS_FROM AND "ORD_DT"=:V_DT AND "SHOP_NO"=:V_SHOP AND "ORD_HMS"<=:V_HMS_TO)
       filter(("ORD_DT"=:V_DT AND "SHOP_NO"=:V_SHOP))

문제

조민우 안의 3번 줄이 313블록을 읽는 이유를 access와 filter로 설명하시오. 메모의 기준이 왜 틀렸는지도 설명하시오.
이 조회에 맞는 인덱스를 설계해 DDL을 작성하시오. = 조건 컬럼 둘의 순서는 무엇으로 정하는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

DROP INDEX ORD_RANDOM_X09;

-- = 로 들어오는 매장 · 날짜를 앞에, 범위인 시각을 맨 뒤에 — '그 매장의 그날 30분 앞뒤' 가 한 구간
CREATE INDEX ORD_RANDOM_X05 ON ORD_RANDOM (SHOP_NO, ORD_DT, ORD_HMS) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 → T2 → VISIBLE

SELECT ORD_NO, ORD_HMS          -- SQL 은 그대로
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop
   AND ORD_DT  = :v_dt
   AND ORD_HMS BETWEEN :v_hms_from AND :v_hms_to
 ORDER BY ORD_HMS;

개선 후 실행계획 학습용 예시 · 인덱스 규모는 교재 04화 실측

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |      1 |00:00:00.01 |       4 |
|*  1 |  FILTER                               |                |      1 |        |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID         | ORD_RANDOM     |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN                   | ORD_RANDOM_X05 |      1 |      1 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------
   1 - filter(:V_HMS_FROM<=:V_HMS_TO)
   3 - access("SHOP_NO"=:V_SHOP AND "ORD_DT"=:V_DT AND "ORD_HMS">=:V_HMS_FROM AND "ORD_HMS"<=:V_HMS_TO)

모범 답안 서술

조민우 안의 3번 줄은 access에 네 조건이 다 적혀 있지만, filter에 ORD_DT와 SHOP_NO가 또 나온다. 인덱스가 ORD_HMS로 먼저 정렬되어 있고 ORD_HMS가 BETWEEN 범위로 들어왔기 때문이다. 범위 조건 뒤의 ORD_DT와 SHOP_NO는 =인데도 구간을 줄이지 못하고 filter로 거른다. 그래서 2년 치, 200개 매장의 17:30 ~ 18:30 항목 약 71,000개(리프 약 311블록)를 다 읽고 1건을 찾는다(Buffers 314). 기존 X01은 그날의 한 시간 치 99건만 읽고 테이블에서 매장을 걸러 100블록이었다. 메모의 기준이 틀렸다. 컬럼 순서의 첫 기준은 종류 수(선택도)가 아니라 조건의 모양이다. 늘 =로 들어오는 컬럼을 앞에 두고 범위 조건 컬럼은 맨 뒤에 둔다. ORD_HMS는 50,400종으로 가장 많지만 이 조회에서는 범위로 들어오므로 맨 뒤에 가야 한다. (SHOP_NO, ORD_DT, ORD_HMS)로 만들면 'SH0123의 11월 2일 17:30 ~ 18:30'이 인덱스 안에서 한 구간이 되어 리프 한 블록 안의 1항목만 읽는다. Buffers는 조민우 안 314 → 4, 기존 X01 100 → 4다. 테이블에 가기 전에 매장까지 걸렀으므로 테이블도 1건만 찾아가고, ORDER BY ORD_HMS도 인덱스 순서 그대로라 정렬이 없다. = 조건 둘(SHOP_NO, ORD_DT)의 순서는 이 SQL에서는 차이가 없다. 어느 쪽을 앞에 둬도 같은 구간이다. 그래서 순서는 다른 SQL을 보고 정한다. 매장 앱처럼 SHOP_NO = :v AND ORD_DT BETWEEN …이 함께 쓰이면 SHOP_NO가 앞이어야 하고, ORD_DT = :v만 주는 SQL이 많다면 ORD_DT가 앞이 낫다. 조민우 안 X09는 지우고, 새 인덱스는 INVISIBLE로 재 본 뒤 VISIBLE로 바꾼다.

해설

ACC-001과 같은 기준이다. 다른 점은 범위로 들어오는 컬럼이 종류가 가장 많은 컬럼이라는 것이다. '종류가 많을수록 앞'이라는 말을 따르면 범위 조건이 맨 앞에 와서 나머지 =가 모두 filter가 된다.
기존 X01이 100블록으로 나쁘지 않았던 이유는 ORD_DT가 =로 들어와 '그날의 한 시간'만 읽었기 때문이다. 다만 매장이 인덱스에 없어서 99건을 테이블에서 걸렀고, CF가 999,958이라 99건이 거의 99블록이 됐다.
범위 조건 컬럼이 맨 뒤에 있으면 그 범위도 access로 쓰인다. 범위 조건이 둘 이상이면 앞의 범위 하나만 구간을 정한다(ACC-017).
새 인덱스의 대가는 주문을 INSERT할 때마다 인덱스 하나를 더 고치는 적재 비용이다. 하루 수백 번 쓰는 클레임 조회가 100 → 4로 주는 값과 견주어 T2에 적는다(교재 04화 §9).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 규모는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 규모는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황