萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 跳躍式索引掃描 核心原理、觸發條件與效能邊界實測

Index Skip Scan (ISS) 是 Oracle 9i 引入的優化器特性,允許在複合索引前導列未出現在 WHERE 條件時,通過「邏輯拆分子索引」機制仍能利用索引。本文深度解析 ISS 觸發機制、成本模型、基數門檻實測、Hint 強制與避坑指南。

核心機制:邏輯子索引拆分

對於複合索引 IDX(A, B),Oracle 根據前導列 A 的**區分度 (NDV, Number of Distinct Values)**,邏輯上將索引拆分為 NDV(A) 個子索引,每個子索引對應 A 的一個唯一值,內部按 B 排序。查詢 WHERE B = :val 時,優化器評估掃描所有子索引的成本:

ISS Cost ≈ NDV(A) × (單子索引掃描成本) + 表訪問成本

當 NDV(A) 足夠小 (通常 < 1000,實測閾值約 37-50) 時,ISS 成本低於全表掃描,優化器選擇 ISS。

必要觸發條件 (缺一不可)

  1. 優化器模式 = CBO (Cost-Based Optimizer),OPTIMIZER_MODE=ALL_ROWS/FIRST_ROWS,非 RBO。
  2. 表已收集統計信息:DBMS_STATS.GATHER_TABLE_STATS,確保 NDV、密度、直方圖準確。
  3. 複合索引存在,且查詢條件**不包含前導列**,但包含非前導列。
  4. 前導列 NDV 足夠小:實測閾值約 37-50 (視數據分佈、表大小、參數 _optimizer_skip_scan_guess 而定)。
  5. 版本 ≥ 9i。

實測閾值驗證 (100 萬行測試表)

CREATE TABLE test_iss (a NUMBER, b CHAR(10), c VARCHAR2(10));
CREATE INDEX test_iss_idx ON test_iss(a, b);

-- 測試 NDV=37 時
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO test_iss VALUES (MOD(i,37), TO_CHAR(i), TO_CHAR(i));
  END LOOP;
  COMMIT;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_ISS');

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM test_iss WHERE b = '500';
-- 執行計劃:TABLE ACCESS FULL (Cost=37)  -- 未選擇 ISS,因 NDV=37 邊界附近

-- 測試 NDV=10 時
TRUNCATE TABLE test_iss;
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO test_iss VALUES (MOD(i,10), TO_CHAR(i), TO_CHAR(i));
  END LOOP;
  COMMIT;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST_ISS');

SELECT * FROM test_iss WHERE b = '500';
-- 執行計劃:INDEX (SKIP SCAN) OF 'TEST_ISS_IDX' -- 選擇 ISS!

實測結論:NDV 閾值約為 37-50

  • NDV=10 → 選擇 ISS (Cost 遠小於 FTS)
  • NDV=37 → 邊界,優化器傾向 FTS (Cost 接近)
  • NDV=100 → 明確選擇 FTS
  • 隱含參數 _optimizer_skip_scan_guess (默認 TRUE) 會影響成本估算,可關閉觀察純邏輯成本。

Hint 強制與禁止

-- 強制使用 ISS (僅 CBO 下生效)
SELECT /*+ INDEX_SS(t test_iss_idx) */ * FROM test_iss t WHERE b = '500';

-- 禁止使用 ISS
SELECT /*+ NO_INDEX_SS(t test_iss_idx) */ * FROM test_iss t WHERE b = '500';

-- 指定索引但不指定掃描方式 (優化器自主選擇 RANGE/SS/FULL)
SELECT /*+ INDEX(t test_iss_idx) */ * FROM test_iss t WHERE b = '500';

效能邊界與適用場景

最佳實踐建議

  1. 設計階段:若業務常按非前導列查詢,考慮將低基數列置於複合索引前導位置,或建立單獨索引。
  2. 統計維護:定期收集統計信息,特別是直方圖 (METHOD_OPT=>'FOR COLUMNS SIZE AUTO ...'),確保 NDV 準確。
  3. 監控識別:V$SQL_PLAN.OPERATION = 'INDEX SKIP SCAN' 記錄 ISS 使用情況,結合 V$SQL.ELAPSED_TIME 評估效果。
  4. 避免濫用 Hint:INDEX_SS 可能隨數據增長變為劣化計劃,優先優化統計信息與索引設計。
copyright © 萬盛學電腦網 all rights reserved
場景是否適合 ISS說明
性別/狀態/類型等低基數前導列 + 高基數非前導列查詢✅ 極佳NDV(前導列) 通常 < 10,ISS 成本極低
前導列 NDV > 1000❌ 不適合子索引過多,成本遠超 FTS,優化器不會選擇
前導列有直方圖且數據傾斜嚴重⚠️ 謹慎熱門值對應子索引大,冷門值小,成本估算失真
查詢返回大量行 (> 5-10% 表行數)❌ 不適合大量 TABLE ACCESS BY ROWID 導致隨機 I/O 暴增,FTS 更優
索引包含所有查詢欄位 (Covering Index)✅ 適合避免回表,ISS 優勢最大化