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。
OPTIMIZER_MODE=ALL_ROWS/FIRST_ROWS,非 RBO。DBMS_STATS.GATHER_TABLE_STATS,確保 NDV、密度、直方圖準確。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!
_optimizer_skip_scan_guess (默認 TRUE) 會影響成本估算,可關閉觀察純邏輯成本。-- 強制使用 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';
| 場景 | 是否適合 ISS | 說明 |
|---|---|---|
| 性別/狀態/類型等低基數前導列 + 高基數非前導列查詢 | ✅ 極佳 | NDV(前導列) 通常 < 10,ISS 成本極低 |
| 前導列 NDV > 1000 | ❌ 不適合 | 子索引過多,成本遠超 FTS,優化器不會選擇 |
| 前導列有直方圖且數據傾斜嚴重 | ⚠️ 謹慎 | 熱門值對應子索引大,冷門值小,成本估算失真 |
| 查詢返回大量行 (> 5-10% 表行數) | ❌ 不適合 | 大量 TABLE ACCESS BY ROWID 導致隨機 I/O 暴增,FTS 更優 |
| 索引包含所有查詢欄位 (Covering Index) | ✅ 適合 | 避免回表,ISS 優勢最大化 |