상품 인덱스를 만들고 힌트까지 넣었는데 상품 실적 조회가 FULL과 똑같이 896블록을 읽는다

중급SELECT한빛유통98 / 700

상황

상품팀의 「상품별 판매 실적」은 상품 하나를 고르면 지금까지 팔린 줄 수와 수량, 금액을 보여 준다. MD 열 명이 하루 수백 번 연다. 지난주 조민우가 이 화면을 위해 IX_T_ORD_DET_ITM (ITM_ID)를 만들었다. 옵티마이저가 그 인덱스를 쓰지 않자 INDEX 힌트를 넣었는데, 힌트를 넣으나 빼나 Buffers는 900 안팎이다. 조민우는 "인덱스가 깨진 것 같으니 REBUILD를 해 보겠다"고 한다. 아래는 상품 ITM00037이다.

테이블과 인덱스

T_ORD_DET: 200,000행 · 896블록(블록당 약 223행) · (ORD_SEQ, ORD_DET_NO) 순으로 쌓였다 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)
ITM_ID 100종, 상품마다 2,000줄 · 상품 번호는 (ORD_SEQ + ORD_DET_NO)를 100으로 나눈 나머지로 매겨져, 쌓인 순서로 보면 약 100줄마다 같은 상품이 돌아온다
인덱스 IX_T_ORD_DET_ITM (ITM_ID): BLEVEL 1 · 리프 약 600블록 · CLUSTERING_FACTOR 89,600 · 이 화면만 쓴다 · 통계 최신

원본 SQL

sql

SELECT /*+ INDEX(d IX_T_ORD_DET_ITM) */
       COUNT(*) AS line_cnt, SUM(d.ORD_QTY) AS qty, SUM(d.ORD_QTY * d.UNT_PRC) AS amt
  FROM T_ORD_DET d
 WHERE d.ITM_ID = :v_itm

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

xplan

[힌트를 넣은 지금]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |      1 |00:00:00.01 |     903 |
|   1 |  SORT AGGREGATE                      |                  |      1 |      1 |      1 |00:00:00.01 |     903 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_DET        |      1 |   2000 |   2000 |00:00:00.01 |     903 |
|*  3 |    INDEX RANGE SCAN                  | IX_T_ORD_DET_ITM |      1 |   2000 |   2000 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------------------------------------
   3 - access("D"."ITM_ID"=:V_ITM)

[힌트 없이: 옵티마이저가 고른 계획]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |      1 |00:00:00.01 |     896 |
|   1 |  SORT AGGREGATE                      |                  |      1 |      1 |      1 |00:00:00.01 |     896 |
|*  2 |   TABLE ACCESS FULL                  | T_ORD_DET        |      1 |   2000 |   2000 |00:00:00.01 |     896 |
------------------------------------------------------------------------------------------------------------------
   2 - filter("D"."ITM_ID"=:V_ITM)

문제

상품 하나의 2,000줄(전체의 1%)을 인덱스로 찾았는데 테이블을 896블록이나 읽은 이유를, CLUSTERING_FACTOR와 상품 번호가 매겨진 규칙으로 설명하시오. 옵티마이저가 힌트 없이는 이 인덱스를 고르지 않은 이유도 설명하시오.
이 화면이 테이블을 읽지 않도록 SQL과 인덱스를 고치시오. REBUILD 제안을 어떻게 판단하는지와 고친 안의 대가도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품으로 찾고 · 집계에 쓰는 ORD_QTY · UNT_PRC 까지 담아 테이블에 가지 않는다 (CF 를 피한다)
CREATE INDEX IX_T_ORD_DET_ITM_QP ON T_ORD_DET (ITM_ID, ORD_QTY, UNT_PRC) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 → T2 → VISIBLE

SELECT COUNT(*) AS line_cnt, SUM(d.ORD_QTY) AS qty, SUM(d.ORD_QTY * d.UNT_PRC) AS amt
  FROM T_ORD_DET d                                            -- 강제 힌트를 뗀다
 WHERE d.ITM_ID = :v_itm;

-- 기존 (ITM_ID) 는 새 인덱스의 접두부 · 이 화면만 썼으므로 INVISIBLE → 관찰 → DROP
ALTER INDEX IX_T_ORD_DET_ITM INVISIBLE;

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

xplan

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  SORT AGGREGATE    |                     |      1 |      1 |      1 |00:00:00.01 |       9 |
|*  2 |   INDEX RANGE SCAN | IX_T_ORD_DET_ITM_QP |      1 |   2000 |   2000 |00:00:00.01 |       9 |
-------------------------------------------------------------------------------------------------
   2 - access("D"."ITM_ID"=:V_ITM)

모범 답안 서술

1번 계획의 3번 줄(인덱스)은 7블록뿐이고, 2번 줄 테이블 액세스에서 896블록을 읽었다. 테이블은 (ORD_SEQ, ORD_DET_NO) 순으로 쌓였고 상품 번호는 그 순서대로 100개를 돌아가며 매겨졌다. 그래서 같은 상품이 약 100줄마다 한 번씩 나온다. 블록 하나에 223줄이 들어가므로 모든 블록에 그 상품이 두세 줄씩 있다. CLUSTERING_FACTOR 89,600을 상품 100개로 나누면 상품 하나에 896, 곧 테이블 블록 수와 같다. 전체 행의 1%를 찾았지만 블록으로는 100%를 방문한 셈이다. 손익분기로 보면 (LEAF 600 + CF 89,600) × s = 896에서 s가 약 0.99%다. 상품 하나의 선택도가 정확히 1%이므로 이 인덱스는 손익분기에 걸쳐 있다. 블록 수가 같다면 FULL은 여러 블록을 한 번에 읽고(멀티블록) 인덱스 경로는 블록을 하나씩 찾아가므로, 옵티마이저는 FULL을 더 싸게 계산했다. 옵티마이저가 옳았고, 힌트는 이득 없이 7블록만 더했다(896 → 903). SQL이 쓰는 컬럼은 ITM_ID, ORD_QTY, UNT_PRC 셋뿐이다. (ITM_ID, ORD_QTY, UNT_PRC) 커버링 인덱스를 만들면 상품 하나의 2,000항목(리프 약 8블록)만 읽고 테이블에는 가지 않는다(Buffers 903 → 9). CF는 그대로 나빠도 테이블에 가지 않으니 청구되지 않는다. 옵티마이저가 스스로 이 인덱스를 고르므로 강제 힌트는 뗀다. REBUILD는 처방이 아니다. CF는 테이블 행이 놓인 순서에서 나오는 값이라 인덱스를 다시 만들어도 89,600 그대로다. 대가는 다음과 같다. 주문상세를 INSERT할 때 고칠 인덱스가 늘지 않도록, 접두부인 기존 (ITM_ID) 인덱스는 이 화면만 썼다는 것을 확인하고 정리한다. 커버링은 지금의 집계 컬럼 목록을 전제로 하므로 화면에 컬럼이 늘면 다시 테이블 액세스가 돌아온다. T2 승인서에 그 목록을 적는다.

해설

함정: "1%만 읽으니 인덱스가 맞다"는 행의 비율만 본 판단이다. 손익분기는 행이 아니라 블록으로 따진다. 고르게 섞여 있는 값은 아주 적어도 모든 블록에 나타난다.
테이블을 ITM_ID 순으로 다시 쌓으면 CF는 좋아진다. 하지만 PK(ORD_SEQ) 순서가 흩어져 주문 한 건의 상세를 읽는 조인(교재 06화의 NL 조인)이 모두 나빠진다. 테이블은 한 가지 순서로만 쌓을 수 있다(ACC-032).
JOIN-015에서는 이 인덱스를 상품 100개만큼 반복해 90,204블록을 읽었다. 같은 인덱스가 어느 SQL에서는 쓸모가 있고 어느 SQL에서는 해가 된다.
상품별 누적 실적이 하루 수백 번 필요하다면, 밤에 한 번 집계해 두는 요약 테이블도 대안이다. 다만 오늘 판매분은 따로 더해야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황