인덱스만 읽는 SQL인데 가맹점주 앱이 무겁다

중급SELECT버거79 / 700

상황

버거원 가맹점주 앱의 「우리 매장 주문 추이」는 자기 매장의 최근 석 달 일별 주문 건수를 보여 준다. 가맹점 200곳이 하루에 수십 번씩 연다. 옵티마이저는 04화에서 월마감용으로 들인 커버링 인덱스 ORD_RANDOM_X02를 골랐고, 테이블에는 가지 않는다. 그런데 매장 하나의 641건을 세는 데 인덱스를 475블록이나 읽는다.

테이블과 인덱스

ORD_RANDOM: 1,000,000행 · 22,414블록 · ORD_DT VARCHAR2(8) 2012 ~ 2013 · 한 달 약 41,666건 · SHOP_NO 'SH0001' ~ 'SH0200'(고르게 분포)
2013년 10 ~ 12월: 약 125,000건 · 그중 매장 SH0042는 641건
인덱스 ORD_RANDOM_X01 (ORD_DT, ORD_HMS) · ORD_RANDOM_X02 (ORD_DT, SHOP_NO): X02는 BLEVEL 2 · 리프 3,774 · 04화 T2 승인 뒤 VISIBLE · 월마감 「가맹점별 주문 건수」(ORD_DT BETWEEN … GROUP BY SHOP_NO, 한 달 160블록)가 쓴다

원본 SQL

sql

SELECT ORD_DT, COUNT(*) AS ord_cnt
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop
   AND ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY ORD_DT
 ORDER BY ORD_DT

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

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |      1 |        |     92 |00:00:00.01 |     475 |
|   1 |  SORT GROUP BY NOSORT |                |      1 |     61 |     92 |00:00:00.01 |     475 |
|*  2 |   FILTER              |                |      1 |        |    641 |00:00:00.01 |     475 |
|*  3 |    INDEX RANGE SCAN   | ORD_RANDOM_X02 |      1 |     61 |    641 |00:00:00.01 |     475 |
---------------------------------------------------------------------------------------------------

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

문제

테이블에 가지도 않는데 인덱스를 475블록이나 읽는 이유를 Predicate Information의 3번 줄을 근거로 설명하시오.
이 SQL에 맞는 인덱스를 설계하고 DDL을 작성하시오. 그 인덱스를 X02를 바꿔서 만들지 하나 더 만들지, 월마감 SQL에 어떤 영향이 있는지까지 따져 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- = 로 들어오는 SHOP_NO 를 앞에 · 범위인 ORD_DT 를 뒤에 — '그 매장의 석 달' 이 한 구간
CREATE INDEX ORD_RANDOM_X03 ON ORD_RANDOM (SHOP_NO, ORD_DT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 내 세션에서만 — 앱 SQL 과 월마감 SQL 을 둘 다 잰다

SELECT ORD_DT, COUNT(*) AS ord_cnt          -- SQL 은 그대로
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop
   AND ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY ORD_DT
 ORDER BY ORD_DT;

-- 월마감은 /*+ INDEX_SS(o ORD_RANDOM_X03) */ 로 'X03 하나만 남았을 때' 를 잰다
--   견디면 → X03 VISIBLE · X02 INVISIBLE → 관찰 뒤 DROP (인덱스 하나로 둘을 감당)
--   못 견디면 → 둘 다 둔다 (적재 비용을 T2 에 적는다)

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

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |      1 |        |     92 |00:00:00.01 |       5 |
|   1 |  SORT GROUP BY NOSORT |                |      1 |     61 |     92 |00:00:00.01 |       5 |
|*  2 |   FILTER              |                |      1 |        |    641 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN   | ORD_RANDOM_X03 |      1 |     61 |    641 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - access("SHOP_NO"=:V_SHOP AND "ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

모범 답안 서술

3번 줄의 access에 "SHOP_NO"=:V_SHOP이 있지만 filter에도 같은 조건이 또 있다. X02는 ORD_DT로 먼저 정렬되어 있어서, 석 달 안의 SH0042 항목은 날짜마다 200개 매장 사이에 흩어져 있다. 범위 조건인 ORD_DT 뒤의 SHOP_NO는 읽을 구간을 줄이지 못하고 filter로 거를 뿐이다. 그래서 석 달 치 약 125,000항목(리프 약 472블록)을 읽고 641건을 남긴다. 테이블에 가지 않아도 475블록이다. =로 들어오는 SHOP_NO를 앞에, 범위 조건인 ORD_DT를 뒤에 둔 (SHOP_NO, ORD_DT)라면 '이 매장의 석 달'이 한 구간이 된다. 리프 3블록만 읽으므로 Buffers는 475 → 5다. 이 인덱스도 SQL이 쓰는 컬럼을 다 담고 있어서 테이블에 가지 않는다. X02를 바꿀지 하나 더 만들지는 월마감 SQL을 보고 정한다. 월마감은 ORD_DT BETWEEN … GROUP BY SHOP_NO라서, (SHOP_NO, ORD_DT) 하나만 남기면 선두 조건을 잃는다. 다만 선두 SHOP_NO가 200종뿐이므로 INDEX SKIP SCAN으로 매장마다 한 번씩 건너뛰며 찾을 수 있다. 매장당 몇 블록 × 200이라 160보다는 늘지만 FULL 22,234보다는 훨씬 적을 것이다. 세션에서 INDEX_SS로 재 보고 견딜 만하면 X02를 INVISIBLE → 관찰 → DROP 순서로 정리해 인덱스 하나로 두 SQL을 감당한다. 견디지 못하면 둘 다 둔다. 그러면 주문을 INSERT할 때마다 인덱스를 하나 더 고치게 되므로(적재 비용), 그 숫자를 T2에 적는다.

해설

ACC-001과 같은 원리다. = 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 둔다. 다른 점은 이미 다른 SQL이 쓰고 있는 인덱스라는 것이다. 순서를 바꾸는 일은 새로 만드는 일보다 위험하므로, 그 인덱스를 쓰던 SQL부터 전수 조사한다(V$SQL_PLAN, DBA_INDEX_USAGE).
SKIP SCAN은 IDX-006에서는 독이었지만(선두 50만 종) 여기서는 약이 될 수 있다(선두 200종). 같은 기능이 선두 컬럼의 종류 수에 따라 독이 되기도 하고 약이 되기도 한다.
매장 앱처럼 '매장 = :v' 조건이 늘 붙는 화면이 많을수록 SHOP_NO 선두 인덱스의 가치가 커진다. 반대로 매장 조건이 없는 집계가 많으면 ORD_DT 선두가 맞다. 순서는 SQL 목록을 보고 정한다.
ORD_DT가 문자라서 석 달의 E-Rows를 61로(실제 641) 낮게 본다. 여기서는 실행계획을 맞게 골랐지만, 다른 SQL에서는 이 오추정 때문에 실행계획이 틀어진다(교재 03화, 04화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황