索引類型速覽
- B-Tree 索引：預設、最通用、支援範圍/排序/連接。
- 位圖索引：低基數欄位（性別、狀態）、數倉星型查詢、不適合高併發 OLTP。
- 函數索引：索引 `UPPER(name)` 等運算式，解決函數導致索引失效。
- 反向鍵索引：反轉鍵值分散插入熱點，適合序號主鍵高併發插入。
- 索引組織表 (IOT)：資料即索引，主鍵存取極快，次要索引維護複雜。
五大核心收益
- 強制唯一性：唯一索引/主鍵約束，保證業務鍵不重複。
- 大幅加速檢索：全表掃描 → 索引範圍掃描，**數量級效能提升**，是建立索引首要理由。
- 加速表連接：外鍵欄位建索引，Nested Loop Join 驅動表高效存取內表，**參考完整性檢查也受益**。
- 優化排序/分組：索引天然有序，`ORDER BY`、`GROUP BY` 可直接讀取索引避免排序操作。
- 輔助優化器決策：索引統計資訊（聚簇因子、唯一值數、直方圖）讓 CBO 更精準估算成本。
四大代價與風險
- 建立/維護耗時：建立索引掃描全表排序；DML 時同步維護 B-Tree，**寫入效能下降**。
- 佔用物理空間：索引段獨立佔用表空間，**聚簇索引/IOT 空間更大**。
- 降低維護速度：每次 INSERT/UPDATE/DELETE 觸發索引維護，**批量載入時建議先 DISABLE INDEX → 載入 → REBUILD**。
- 優化器誤判風險：統計資訊過期/缺失導致選錯索引，反而劣化效能。
為什麼不給每個欄位建索引？
**邊際效益遞減 + 邊際成本遞增。** 索引是「精準手術刀」，非「廣譜抗生素」。
應建立索引的欄位特徵（高收益場景）
- 頻繁出現在 `WHERE` 條件欄位（選擇性高 > 1-5%）
- 主鍵/唯一鍵欄位（強制約束）
- 頻繁用於 `JOIN ON` 的外鍵欄位
- 頻繁用於 `ORDER BY`/`GROUP BY` 的欄位
- 範圍查詢欄位（`BETWEEN`、`>`、`<`、`LIKE 'prefix%'`）
不應建立索引的欄位特徵（低收益/負收益場景）
| 特徵 | 理由 | 典型案例 |
|---|
| 查詢極少引用 | 維護成本 > 查詢收益 | 備註欄、擴充屬性 |
| 基數極低 | 索引選擇性差，優化器傾向全表掃描 | 性別、狀態碼（0/1/2）、是否刪除旗標 |
| 大物件型別 | 索引結構不支援/極大 | CLOB、BLOB、BFILE、LONG |
| 高頻異動、低頻查詢 | 寫入代價遠超讀取收益 | 日誌表序號、臨時狀態欄位 |
索引策略黃金法則
- 以業務查詢模式為導向，而非表結構。分析 Top SQL、執行計畫、AWR/ASH 報告。
- 複合索引遵循「最左前綴」與「選擇性遞減」原則。 `INDEX (a,b,c)` 支援 `a`、`a,b`、`a,b,c`，不支援 `b`、`b,c`。
- 定期監控索引使用情況： `V$OBJECT_USAGE` (監控模式)、`DBA_HIST_SQL_PLAN`、AWR 索引掃描統計。**長期未用索引果斷 DROP**。
- 批量載入/重構時： `ALTER INDEX ... UNUSABLE` → 載入 → `REBUILD ONLINE`，避免大量 Redo/Undo。
- 12c+ 新特性： 部分索引、自動索引（Autonomous DB）、索引壓縮進階選項。
