점장 앱의 월 판매 추이가 474블록을 읽는데 인덱스는 하나만 더 만들 수 있다

실전DDL유통91 / 700

상황

본사 레거시 유통의 판매 테이블 CF10을 네 화면이 조회한다. 점장 앱의 「월 판매 추이」가 느리다는 말이 나와 살펴보니, 다른 셋도 같은 테이블을 쓴다. 박팀장의 지시는 이렇다. "POS 쪽 INSERT가 걱정이다. 인덱스는 하나만 더 만들 수 있다. 무엇을 어떤 순서로 만들고 기존 것은 어떻게 할지, 숫자로 적어 와라."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 판매 날짜순으로 적재 · 2022-01-01 ~ 2023-12-31(730일) · 하루 596 ~ 793건 · 매장 다섯 곳(1001 ~ 1005)에 약 10만 건씩 · POS가 판매마다 INSERT한다
매장 1003: 2023-12-15 156건 · 2023년 12월 4,085건
전 매장: 2023-12-15 681건 · 2023년 12월 20,864건
인덱스 CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CLUSTERING_FACTOR 9,921(교재 03화 실측)
새 인덱스 후보의 규모: (SHP_NO, SLE_DT)든 (SLE_DT, SHP_NO)든 리프 약 1,470블록(리프 한 블록에 약 340항목)

원본 SQL

sql

-- S1 POS 「오늘 판매 목록」: 매장 POS 가 수시로 (하루 수천 번)
SELECT SLE_NO, SLE_HMS
  FROM CF10
 WHERE SHP_NO = :v_shp
   AND SLE_DT = :v_dt
 ORDER BY SLE_NO DESC;

-- S2 점장 앱 「월 판매 추이」: 점장 다섯 명이 하루 수십 번씩
SELECT SLE_DT, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SHP_NO = :v_shp
   AND SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SLE_DT
 ORDER BY SLE_DT;

-- S3 본사 「일 마감」: 매장별 하루 건수 · 하루 몇 번
SELECT SHP_NO, COUNT(*) AS sle_cnt
  FROM CF10
 WHERE SLE_DT = :v_dt
 GROUP BY SHP_NO;

-- S4 본사 「월 매출 집계」: CF11 과 조인 · 한 달에 한 번 (교재 03화의 쿼리)
SELECT a.SHP_NO, SUM(b.SLE_QTY * b.SLE_UPRC) AS sle_amt
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN :v_from AND :v_to
   AND b.SLE_NO = a.SLE_NO
 GROUP BY a.SHP_NO;

실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로 · 기존 인덱스 규모는 교재 03화 실측

xplan

[S2 — 지금 · 매장 1003 · 2023년 12월]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |     31 |00:00:00.03 |     474 |
|   1 |  SORT GROUP BY                        |                |      1 |    535 |     31 |00:00:00.03 |     474 |
|*  2 |   FILTER                              |                |      1 |        |   4085 |00:00:00.03 |     474 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    535 |   4085 |00:00:00.03 |     474 |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |   2673 |  20864 |00:00:00.01 |      60 |
----------------------------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - filter("SHP_NO"=:V_SHP)
   4 - access("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO)

[S1 — 지금 · 매장 1003 · 2023-12-15]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    156 |00:00:00.01 |      19 |
|   1 |  SORT ORDER BY                        |                |      1 |    137 |    156 |00:00:00.01 |      19 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |    137 |    156 |00:00:00.01 |      19 |
|*  3 |    INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |    685 |    681 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------------
   2 - filter("SHP_NO"=:V_SHP)
   3 - access("SLE_DT"=:V_DT)

[S3 — 지금] IX_CF10_SLE_DT 로 681건 → 테이블에서 SHP_NO 를 읽는다 · 19
[S4 — 교재 03화 실측 · 2023년 6월] 전체 8,552 · CF10 쪽 INDEX RANGE SCAN 62 → 테이블 478 · CF11 FULL 8,074

문제

S2가 474블록을 읽는 이유를 실행계획의 줄 번호와 숫자로 설명하시오.
새 인덱스 하나의 컬럼과 순서를 정해 DDL을 작성하시오. (SHP_NO, SLE_DT)와 (SLE_DT, SHP_NO)를 S1 ~ S4 각각에서 비교하고(access와 filter, 어림 블록 수), 기존 IX_CF10_SLE_DT를 남길지 지울지도 판단하시오.
고른 안의 대가와 검증 절차를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 기존 (SLE_DT) 를 앞부분으로 품는 (SLE_DT, SHP_NO) 로 바꿔 단다 — 인덱스 수는 그대로 둘 (PK + 이것)
CREATE INDEX IX_CF10_DT_SHP ON CF10 (SLE_DT, SHP_NO) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- S1 ~ S4 를 세션에서 잰다

ALTER INDEX IX_CF10_DT_SHP VISIBLE;                         -- T2 승인 뒤
ALTER INDEX IX_CF10_SLE_DT INVISIBLE;                       -- S4 가 도는 월말까지 관찰
DROP INDEX IX_CF10_SLE_DT;                                  -- 다른 SQL 이 안 쓰는 것을 확인한 뒤

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로 · 기존 인덱스 규모는 교재 03화 실측

xplan

[S2 — (SLE_DT, SHP_NO) · 매장 1003 · 2023년 12월]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |     31 |00:00:00.01 |      62 |
|   1 |  SORT GROUP BY NOSORT                 |                |      1 |    535 |     31 |00:00:00.01 |      62 |
|*  2 |   FILTER                              |                |      1 |        |   4085 |00:00:00.01 |      62 |
|*  3 |    INDEX RANGE SCAN                   | IX_CF10_DT_SHP |      1 |    535 |   4085 |00:00:00.01 |      62 |
----------------------------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - access("SLE_DT">=:V_FROM AND "SHP_NO"=:V_SHP AND "SLE_DT"<=:V_TO)
       filter("SHP_NO"=:V_SHP)

[S3 — (SLE_DT, SHP_NO)] access("SLE_DT"=:V_DT) · 테이블에 가지 않는다 · 19 → 4
[S1 — (SLE_DT, SHP_NO)] access("SLE_DT"=:V_DT AND "SHP_NO"=:V_SHP) · 156건 → 테이블 약 7 · 19 → 약 10
[비교 — (SHP_NO, SLE_DT) 였다면] S2 14 (access 둘 다, 테이블에 안 간다) · S3 은 선두 조건이 없어 IX_CF10_SLE_DT 가 필요하거나 SKIP SCAN

모범 답안 서술

S2의 4번 줄은 IX_CF10_SLE_DT로 전 매장의 한 달 치 20,864건을 찾는다(60블록). 인덱스에 매장 번호가 없으므로, 3번 줄 TABLE ACCESS BY INDEX ROWID BATCHED가 그 20,864건의 테이블 블록을 다 찾아간 뒤에 filter("SHP_NO"=:V_SHP)로 매장 1003의 4,085건만 남긴다(테이블 414블록). 474블록의 대부분은 거를 행을 읽으러 테이블에 간 몫이다. 두 순서를 S1 ~ S4에서 비교한다. (SHP_NO, SLE_DT)는 S1과 S2에서 두 컬럼이 모두 access다. S2는 '1003의 12월' 4,085항목(리프 약 12블록)만 읽고 테이블에 가지 않아 14블록이다. 그러나 S3은 선두 SHP_NO 조건이 없으므로 IX_CF10_SLE_DT를 남기거나, 선두 값 다섯 개를 건너뛰는 SKIP SCAN에 기대야 한다. S4도 IX_CF10_SLE_DT가 있어야 지금 그대로다. (SLE_DT, SHP_NO)는 S1에서 두 컬럼이 모두 access다. S2에서는 범위 조건 SLE_DT 뒤의 SHP_NO가 filter라서 전 매장 한 달 치 20,348항목(리프 약 60블록)을 읽지만, 매장 번호가 인덱스에 있어 테이블에는 가지 않으므로 62블록이다. S3은 SLE_DT =로 시작하고 SHP_NO까지 인덱스에 있어 19 → 4블록이다. S4는 SLE_DT가 선두라 기존 인덱스가 하던 일을 그대로 한다. (SLE_DT, SHP_NO)를 고른다. 기존 (SLE_DT)는 이 인덱스의 앞부분이라 새 인덱스가 그 일을 다 대신한다(접두 중복). 그래서 새 인덱스를 만들고 IX_CF10_SLE_DT를 지우면 인덱스 수가 늘지 않고, POS INSERT가 고치는 인덱스도 지금처럼 둘(PK와 이것)이다. S2는 474 → 62, S3은 19 → 4다. (SHP_NO, SLE_DT)는 S2를 14까지 줄이지만, IX_CF10_SLE_DT를 남기면 인덱스가 셋이 되고 지우면 S3과 S4가 SKIP SCAN에 기대야 한다. S2가 하루 수백 번이라 62와 14의 차이는 하루 1만 블록 남짓이다. 이 정도라면 인덱스를 늘리지 않는 쪽이 낫다. 대가도 있다. 새 인덱스는 기존보다 리프가 80블록쯤 크다(1,393 → 약 1,470). 같은 날짜 안에서 매장별로 다시 정렬되므로 CLUSTERING_FACTOR가 기존 9,921보다 나빠지고, S4의 CF10 쪽 테이블 액세스가 늘어난다. 한 달에 한 번 도는 집계라 감수한다. 검증은 새 인덱스를 INVISIBLE로 만들어 세션에서 S1 ~ S4를 재고, VISIBLE로 바꾼 뒤 IX_CF10_SLE_DT를 INVISIBLE로 돌려 S4가 도는 월말까지 관찰한다. DBA_INDEX_USAGE와 V$SQL_PLAN으로 다른 SQL이 그 인덱스를 쓰지 않았는지 확인한 뒤 지운다.

해설

'= 조건 앞, 범위 조건 뒤'는 SQL 하나의 일량을 가장 작게 만드는 기준이다. 인덱스 개수에 제약이 있고 SQL이 여럿이면, 원칙에서 한 발 물러선 순서가 전체로는 나을 수 있다. 그때는 원칙에서 벗어난 SQL(여기서는 S2)이 filter로 얼마나 더 읽는지 숫자로 적어 둔다.
다른 정답: (SHP_NO, SLE_DT)를 만들고 IX_CF10_SLE_DT를 남기는 안이다. S2가 키오스크 자동 새로 고침처럼 몇 배로 늘 예정이라면 이쪽이 맞다. 대가는 POS INSERT마다 인덱스 하나를 더 고치는 것이다.
(SHP_NO, SLE_DT)를 만들고 IX_CF10_SLE_DT를 지우는 안도 있다. S3은 선두 값이 다섯 개뿐이라 SKIP SCAN으로 견딜 수 있다. 하지만 옵티마이저가 늘 SKIP SCAN을 고른다는 보장이 없고, S4가 테이블을 매장 순서로 찾아가 CF 손해를 본다. 세션에서 재 보지 않고는 고르지 않는다(ACC-004와 같은 판단).
블록 수가 작아 차이가 작아 보이지만, 같은 판단을 행 수가 100배인 테이블에 하면 숫자도 그대로 100배가 된다. 비율로 비교해 두면 규모가 바뀌어도 판단을 다시 쓸 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로 · 기존 인덱스 규모는 교재 03화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터(CF10) 그대로 · 기존 인덱스 규모는 교재 03화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황