Oracle 索引設計三部曲：對的表、對的欄位、對的數量

核心原則：三個「適當」

索引並非越多越好。設計準則只有一句：在適當的表、適當的欄位、建立適當數量的索引。以下逐層拆解。

一、 判斷「適當的表」：大小與存取模式雙維度

小表：通常不建索引

如部門表（< 100 列），全表掃描極快，索引維護成本（DML 額外 I/O、空間）遠超查詢收益。

大表：需實測「選擇率」

經驗值：常用查詢回傳列數 < 10%~15% 才值得建索引。具體做法：

1. 收集典型 SQL 執行計畫，對比 FULL TABLE SCAN vs INDEX RANGE SCAN 成本。
2. 若全掃更快（如資料高度聚集、區塊擁擠），大膽捨棄索引。
3. 日誌/審計類大表：僅 DBA 偶爾查詢近期資料，建索引反而拖慢批次寫入，不建索引更優。

二、 選擇「適當的欄位」：特徵與業務雙驅動

動態調整案例

某用戶表 `ID_CARD` 列建有唯一索引，上線後發現 90% 列為 NULL，業務從未按身份證查詢。決策：刪除索引，釋放空間與 DML 開銷。索引優化是持續過程，隨資料分布與業務變化定期（如半年）複盤。

三、 控制「適當的數量」：讀寫平衡點

• 查詢導向（DW/BI）： 索引多寡不拘，可上線前停用索引、大量載入後重建。
• 交易導向（OLTP）： 每個索引都是 DML 懲罰。監控 V$SEGMENT_STATISTICS 中 logical reads、db block changes，若 INDEX MAINTENANCE 佔 DML 耗時 > 30%，考慮合併/刪除低選擇率索引。
• 實用技巧： ALTER INDEX ... UNUSABLE 暫時停用觀察，確認無負面影響再 DROP。

進階：複合索引欄位順序

遵循「等值優先、範圍在後、高基數在前」原則。例：WHERE dept_id=10 AND hire_date > '2020-01-01' → 索引 (dept_id, hire_date) 而非反序。

總結清單

1. 小表免建、大表實測選擇率。
2. 高基數、高頻謂詞、外鍵優先建索引。
3. 大量 NULL、從不查詢、頻繁更新的欄位果斷不建/刪索引。
4. OLTP 控制索引總數，DW 重載入策略配合。
5. 定期複盤，索引隨業務演進。