一、 概念釐清：三種索引掃描模式對比
| 掃描類型 | 訪問路徑 | 有序性 | 典型觸發場景 | I/O 模式 |
|---|
| Index Range Scan |
Root → Branch → Leaf (遍歷 B-Tree) |
有序 (按索引鍵) |
WHERE 子句包含索引前導列範圍條件 |
單塊讀取 (db file sequential read) |
| Index Full Scan |
遍歷所有 Leaf 塊 (雙向鏈表) |
有序 |
無範圍條件但需有序結果 (ORDER BY 索引列) |
單塊讀取 |
| Index Fast Full Scan (IFFS) |
直接掃描 Segment 所有塊 (忽略 B-Tree 結構) |
無序 |
查詢列全包含於索引 + 大數據量訪問 + 無需有序 |
多塊讀取 (db file scattered read) |
二、 IFFS 觸發鐵律 (三個必要條件)
- 覆蓋索引： `SELECT` 列表、`WHERE`、`JOIN`、`GROUP BY`、`ORDER BY` 引用的**所有列必須包含在索引中** (或通過 ROWID 回表，但回表會退化為 Range Scan)。
- 大數據量訪問： 優化器預估訪問行數佔表總行數比例較高 (典型閾值 10%-25%)，具體受 `db_file_multiblock_read_count` (MBRC) 和並行度影響。MBRC 越大、並行度越高，傾向 IFFS 的閾值越低。
- 無序要求： 查詢無 `ORDER BY` 索引列，或雖有 `ORDER BY` 但優化器判定「IFFS + SORT」成本低於「Index Full Scan」。
三、 多塊讀取參數：db_file_multiblock_read_count (MBRC)
- 控制 IFFS、全表掃描、全索引掃描每次 I/O 讀取的塊數。
- 默認值：8 (10g-) / 自動調整 (11g+ `_db_file_optimizer_read_count` 衍生)。
- 關鍵影響： MBRC 越大，IFFS 成本計算公式 `Cost = Blocks / MBRC` 越小，**越傾向選擇 IFFS 而非 Range Scan**。
- 系統統計信息 (CPU/IO 速率) 採集後，優化器自動調整有效 MBRC，建議勿手工硬編碼，改用 `DBMS_STATS.GATHER_SYSTEM_STATS`。
四、 Hint 強制與並行化控制
1. INDEX_FFS Hint
-- 基礎強制
SELECT /*+ INDEX_FFS(t idx_emp_dept) */ deptno, COUNT(*)
FROM emp t
GROUP BY deptno;
-- 表別名必須匹配，索引名可省略 (優化器選擇最優索引)
SELECT /*+ INDEX_FFS(e) */ empno, ename FROM emp e WHERE deptno=10;
2. 並行索引掃描：PARALLEL_INDEX 組合
-- 強制並行快速全索引掃描 (DOP=4)
SELECT /*+ INDEX_FFS(e pk_emp) PARALLEL_INDEX(e pk_emp 4) */
deptno, COUNT(*)
FROM emp e
GROUP BY deptno;
-- 或使用 PARALLEL Hint (表級並行，隱含索引並行)
SELECT /*+ PARALLEL(e 4) */ deptno, COUNT(*) FROM emp e GROUP BY deptno;
3. 禁止 IFFS：NO_INDEX_FFS
SELECT /*+ NO_INDEX_FFS(e idx_emp_dept) */ ... -- 強制不使用該索引的 IFFS
五、 實戰案例：顏色統計查詢優化
-- 原始查詢：全表掃描 + HASH GROUP BY
SELECT color, COUNT(*) FROM automobiles GROUP BY color;
-- Plan: TABLE ACCESS FULL AUTOMOBILES (Cost=1200)
-- 創建覆蓋索引
CREATE INDEX idx_auto_color ON automobiles (color);
-- 強制 IFFS + 並行
SELECT /*+ INDEX_FFS(a idx_auto_color) PARALLEL_INDEX(a idx_auto_color 4) */
color, COUNT(*)
FROM automobiles a
GROUP BY color;
-- Plan: INDEX FAST FULL SCAN IDX_AUTO_COLOR (Cost=85) + PX SEND HASH
-- 成本下降 90%+，利用多塊讀取 + 並行 CPU
六、 避坑與診斷指南
| 現象 | 原因 | 排查動作 |
|---|
| 明明建了覆蓋索引，卻走全表掃描 |
統計信息過期/缺失，優化器誤估基數 |
`DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')`；檢查 `NUM_ROWS`, `BLOCKS`, `DISTINCT_KEYS` |
| IFFS 但性能未提升甚至變慢 |
索引塊數接近表塊數 (索引膨脹/表稀疏)；或 MBRC 設置過小 |
檢查 `INDEX_STATS` (BLEVEL, LEAF_BLOCKS) vs `TABLE_STATS` (BLOCKS)；重建索引 `ALTER INDEX ... REBUILD`；採集系統統計 |
| 加了 PARALLEL_INDEX 但無並行 |
參數 `PARALLEL_DEGREE_POLICY=MANUAL` 且表/索引無 `PARALLEL` 屬性 |
`ALTER INDEX idx_auto_color PARALLEL 4`；或 Hint `PARALLEL_INDEX(a idx_auto_color 4)` |
| 查詢含 ORDER BY 索引列，優化器選 IFFS + SORT |
排序成本 < Index Full Scan 成本 (大數據量時常見) |
若需避免排序，用 `INDEX(e idx_auto_color)` 強制 Index Full Scan (有序) |
七、 版本演進：12c+ In-Memory / Zone Maps 影響
- In-Memory Column Store： 列式內存掃描速度遠超 IFFS，優化器傾向 IM 掃描而非 IFFS。
- Zone Maps (Storage Indexes)： Exadata/ADB 自動維護塊級 Min/Max 元數據，可跳過大量索引/表塊讀取，邊緣化 IFFS 優勢。
- Automatic Indexing (19c+)： 自動創建/管理覆蓋索引，IFFS 機會自動增加。
