Oracle 建立索引的 7 大注意事項與最佳實務

前言

索引雙面刃：查詢快、寫入慢。盲目建立索引反而拖垮整體效能。本文彙整 7 項 DBA 實戰經驗，助你在「查詢加速」與「維護成本」間取得平衡。

1. 小表不建索引

資料量極少 (如 < 1,000 筆、區塊數 < 50) 時，全表掃描 I/O 成本極低，優化器通常直接忽略索引。

2. 選擇率低的欄位不建索引

經驗值：若查詢回傳資料 > **10%~15%** 總筆數，全表掃描通常比索引範圍掃描 + TABLE ACCESS BY ROWID 更快。精確門檻需透過 EXPLAIN PLAN 驗證。

3. 高選擇性欄位優先建索引

• 唯一鍵、主鍵、高基數欄位 (如身分證、訂單號) 效果最佳。
• 常用於 JOIN 條件的 FK 欄位建索引可避免全表掃描 (Nested Loops 驅動表)。
• 頻繁查詢「非 NULL」值的稀疏欄位，建立索引並配合 WHERE col IS NOT NULL 可大幅加速。

4. 定期檢視與維護索引

• **重建**：碎片化導致高度增加、空間浪費 → ALTER INDEX idx REBUILD ONLINE;
• **移除**：長期未被使用 (查詢 V$OBJECT_USAGE 需先 ALTER INDEX idx MONITORING USAGE) 的索引應刪除。
• **統計資訊**：DBMS_STATS.GATHER_INDEX_STATS 保持優化器決策準確。

5. 索引數量與 DML 效能的平衡

每新增一個索引，INSERT/UPDATE/DELETE 皆需額外維護索引項目。**OLTP 核心表建議 ≤ 5 個索引**；唯讀/資料倉庫表可放寬。

6. 資料倉庫 vs. OLTP 的索引策略差異

7. 位圖索引的基數誤區

位圖索引適用於**極低基數** (如性別 2、婚姻 3、地區 < 50)。若基數 > 100，位圖索引體積膨脹、合併運算慢，反而不如 B-tree。切勿對「類別代碼」這類中等基數欄位濫用位圖索引。

總結口訣

「小表免索、低選免索、高選必索、常維護、數量可控、場景分策、位圖看基」