Oracle 9i 跳躍式索引 (Index Skip Scan) 深度解析與實測門檻值

什麼是 Index Skip Scan (ISS)

Oracle 9i 新特性。當**複合索引前導欄位不在 WHERE 條件中**，但**前導欄位 Distinct Values 極少**時，CBO 可選擇「邏輯分割」索引為多個子索引，逐一掃描，避免全表掃描。

觸發條件

1. 優化器模式 = **CBO** (已收集統計資訊)。
2. 複合索引 (col1, col2, ...)，查詢條件**不含 col1**，含 col2...。
3. col1 的 Distinct Value (NDV) 非常小 (實測門檻見下)。
4. 可用 Hint 強制：/*+ INDEX_SS(t idx) */。

內部機制推測

Oracle 邏輯上將索引分割為 NDV(col1) 個子索引，每個子索引對應一個 col1 值。掃描成本 ≈ NDV(col1) × 子索引掃描成本。若此成本 < FTS 成本，則選擇 ISS。

實測尋找 NDV 門檻值

環境：TEST1(A NUMBER, B CHAR(10), C VARCHAR2(10))，索引 TEST_IDX1(A, B)，10 萬筆資料。

案例 1：NDV(A) = 37 → 選 FTS

INSERT INTO test1 SELECT MOD(ROWNUM,37), TO_CHAR(ROWNUM), TO_CHAR(ROWNUM) FROM DUAL CONNECT BY LEVEL <= 100000;
ANALYZE TABLE test1 COMPUTE STATISTICS;

SELECT * FROM test1 WHERE b = '500';
-- Plan: TABLE ACCESS FULL (Cost=37)

案例 2：NDV(A) = 36 → 選 ISS

TRUNCATE TABLE test1;
INSERT ... MOD(ROWNUM,36) ...;
ANALYZE ...;

SELECT * FROM test1 WHERE b = '500';
-- Plan: INDEX (SKIP SCAN) OF TEST_IDX1 (Cost=12)
-- FTS Cost 仍為 37，ISS Cost=12 < 37，故選擇 ISS

結論：在該資料量/區塊大小下，**NDV ≤ 36 觸發 ISS，NDV ≥ 37 退回 FTS**。門檻值 = **FTS Cost**。

影響因素進階測試

不滿足 ISS 條件時的替代計劃

NDV(A)=100,000 (唯一值)，查詢 WHERE b='500'：

• SELECT * → FTS (Cost=37)
• SELECT COUNT(*) → INDEX FAST FULL SCAN (Cost=34)
• SELECT a → INDEX FAST FULL SCAN (Cost=34) — 索引覆蓋查詢
• RULE Hint → FTS (RBO 不支援 ISS)

關鍵總結

1. ISS **僅限 CBO**，RBO 完全不支援。
2. 核心門檻：**前導欄位 NDV ≤ FTS Cost** (約等於資料區塊數/多區塊讀取計數)。
3. 8i 任何情況下都不會使用 ISS。
4. 實務上，**刻意設計低 NDV 前導欄位 (如性別、狀態、類型) 建複合索引**，可啟用 ISS 解決「查詢不帶前導欄位」痛點。