Oracle 索引全體系解析：B-Tree、函數、反轉、位圖、分區、IOT、隱藏、虛擬、域索引選型指南

索引是查詢效能核心杠桿。本文系統梳理 Oracle 11 類索引類型：核心 B-Tree 族、數據倉庫位圖族、特殊用途索引、測試輔助索引，附優化器導向選型原則與統計信息維護鐵律。

一、核心 B-Tree 族

1. 標準 B-Tree

• 默認 CREATE INDEX 類型，存儲列值 + ROWID
• 多路查找：Root → Branch → Leaf → Table Access by ROWID
• 優化器權衡：全表掃描 vs 索引範圍掃描 + 表訪問
• 聯合索引前導列原則：INDEX (c, d) 支持 WHERE c=...，不支持 WHERE d=... 單列查詢

2. 函數索引

• 解決函數列查詢無法用索引：CREATE INDEX idx_upper_name ON emp(UPPER(ename))
• 適用：大小寫不敏感查詢、表達式計算列、TO_DATE 格式化列

3. 反轉鍵索引

• 鍵值字節反轉存儲：CREATE INDEX idx_rev ON t(col) REVERSE
• 適用：鍵值前綴高度相似（如 'RESTAURANT_001', 'RESTAURANT_002'），消除熱塊爭用
• 不支持範圍掃描，僅等值查詢

二、數據倉庫位圖族

4. 位圖索引

• 低基數列（性別、狀態、地區）
• 多位圖索引組合（AND/OR）產生高選擇性
• 嚴禁用於高併發 OLTP：DML 鎖定整個位圖段，死鎖高發
• 假設數據靜態/批量載入

5. 位圖連接索引

• 預連接維度表列到事實表索引：CREATE BITMAP INDEX idx_bj ON fact(dim_col) FROM fact, dim WHERE fact.dim_key=dim.dim_key
• 查詢時省略 JOIN，直接索引定位

三、特殊用途索引

四、測試輔助索引（11g+）

• 隱藏索引：CREATE INDEX ... INVISIBLE，對 CBO 不可見，驗證方案無影響生產
• 虛擬索引：CREATE INDEX ... NOSEGMENT，不分配段，僅供優化器評估計劃，GB 級表秒級驗證

五、選型決策矩陣

1. OLTP 高併發 → B-Tree / 函數 / 反轉 / 壓縮 / 降序 / IOT / 隱藏 / 虛擬
2. 數據倉庫/低基數/只讀/批量載入 → 位圖 / 位圖連接 / 本地分區
3. 分區表主鍵/唯一鍵 → 全局分區索引
4. 分區表非唯一 → 本地分區索引
5. 空間/文本/自定義類型 → 域索引

六、優化器導向鐵律

• 統計信息必須最新：DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')
• 持續測試新查詢計劃：EXPLAIN PLAN + DBMS_XPLAN.DISPLAY
• 目標：最小化 Consistent Gets、Physical Reads、Sort/Merge Bytes
• 索引非越多越好：寫入開銷、優化器評估開銷、統計維護成本隨索引數線性增長

索引設計是「業務訪問模式」與「存儲結構」的精準耦合，無通用最佳，只有場景最適。