索引分類速覽
- B-Tree 索引：預設、最通用，支援等值/範圍/排序/連接。
- Bitmap 索引：低基數欄位 (性別、狀態)、數倉星型模型、多條件 AND/OR 組合。
- Function-Based 索引：`UPPER(name)`、`TRUNC(date)` 等函數運算列。
- Partitioned 索引：Local/Global，配合分區表。
- Unique/Primary Key 索引：強制唯一性，自動建立。
索引五大核心優勢
- 強制唯一性：PK/UK 索引保證業務鍵不重複。
- 加速檢索：全表掃描 O(N) → 索引範圍掃描 O(log N)，大表效能提升數量級。
- 加速連接：外鍵欄位建索引，Nested Loop Join 驅動表高效探測內表。
- 優化排序/分組：`ORDER BY`/`GROUP BY` 欄位有索引可省略 Sort 操作。
- 啟用優化器進階路徑：Index Fast Full Scan、Index Skip Scan、Index Join 等。
索引三大代價 (為何不給每列建索引)
- 寫入開銷：每次 `INSERT/UPDATE/DELETE` 需同步維護所有相關索引結構，索引越多、越寬，DML 越慢。
- 空間佔用：索引段佔用額外表空間，聚簇索引 (IOT) 更大。
- 優化器負擔：索引過多導致執行計畫搜尋空間爆炸，可能選錯計畫；統計資訊收集時間增加。
「必建索引」欄位特徵清單
| 特徵 | 典型場景 | 索引類型建議 |
|---|
| 頻繁出現在 WHERE 條件 | 用戶 ID、訂單號、手機號 | B-Tree / Unique |
| 作為主鍵 / 唯一約束 | PK、UK | 自動建立 Unique B-Tree |
| 頻繁用於表連接 (FK) | ORDER.USER_ID → USER.ID | B-Tree (外鍵必建) |
| 頻繁範圍查詢 (BETWEEN, >, <) | 交易日期、金額區間 | B-Tree |
| 頻繁排序/分組 | CREATE_TIME, STATUS | B-Tree (避免 SORT) |
| 低基數 + 多條件組合查詢 | 性別+狀態+地區 | Bitmap 索引 (數倉/OLAP) |
「不建索引」欄位特徵清單
| 特徵 | 理由 | 例外 |
|---|
| 極少查詢引用 | 維護成本 > 查詢收益 | 作為 PK/UK 必建 |
| 極低基數 (如性別、布爾旗標) | 選擇度極低，全表掃描更快；Bitmap 除外 | 數倉星型模型維度表可用 Bitmap |
| 大物件類型 (LOB, LONG, XMLTYPE) | 無法建立標準 B-Tree，需 Oracle Text / XMLIndex | 全文檢索用 Context 索引 |
| 高頻更新、低頻查詢 | 寫入放大嚴重 | 評估業務優先級，或延遲索引維護 (Unusable→Rebuild) |
索引設計黃金法則
- 以查詢負載為導向：收集 `V$SQL`/`AWR` 高頻 SQL，分析 WHERE/JOIN/ORDER BY 欄位組合。
- 組合索引遵循「最左前綴」：`IDX(A,B,C)` 支援 `A`、`A,B`、`A,B,C`，不支援 `B`、`C`、`B,C`。
| 選擇度優先：高選擇度 (唯一值多) 欄位放組合索引最左邊。 |
- 控制索引總數：單表 **建議 ≤ 5-6 個**，超過需嚴格評估 ROI。
- 定期審視與清理：`USER_INDEXES` + `V$OBJECT_USAGE` (監控 `ALTER INDEX ... MONITORING USAGE`) 識別未用索引，果斷 `DROP`。
結語
索引是 **「換空間換時間、換寫入換讀取」** 的經典權衡。沒有萬能索引，只有 **匹配負載的索引組合**。建立「索引變更審批流程：負載分析 → 設計 → 測試驗證 (SQL Plan Baseline) → 上線 → 持續監控 → 定期清理」，讓索引治理從經驗主觀走向數據驅動。
