Oracle 索引優化鐵律：三個「適當」打造高效能資料庫

核心觀念：索引非越多越好，而在於「適當」

建立索引雖簡單，但要達到「適當的表、適當的欄位、適當的數量」三個適當，卻是考驗 DBA 功力的關鍵。盲目建立索引不僅無法提升效能，反而會拖垮寫入效能、增加維護成本。本文系統梳理索引優化的三大決策維度。

一、依「表的大小與存取模式」決定是否建立索引

小表：通常不需要索引

如部門表僅百筆資料，全表掃描在記憶體中瞬間完成，索引帶來的 I/O 減少微乎其微，卻須付出 DML 維護、空間佔用成本。經驗值：資料筆數 < 1,000 或區塊數 < 50 時，全表掃描通常更優。

大表：需實測「存取比例」決定

並非所有大表都適合建索引。關鍵在於常用查詢回傳資料佔全表比例：

• < 10%~15%：建立索引通常有效（範圍查詢、等值查詢）。
• > 15%：全表掃描往往更快（避免大量隨機 I/O）。

驗證方法：對比 EXPLAIN PLAN 中 FULL TABLE SCAN 與 INDEX RANGE SCAN 的 Cost，或實測執行時間。

特殊用途表：如稽核日誌表

僅 DBA 偶爾查詢、寫入頻繁、查詢範圍通常為近期資料 → 不建立索引，改用分區表按時間管理更高效。

二、依「欄位特徵與業務模式」選擇索引鍵值

高選擇性欄位 = 索引首選

• 唯一鍵、主鍵（唯一性約束自動建立索引）。
• 重複值極少的業務欄位（身分證字號、手機號、訂單編號）。

常用於 WHERE、JOIN、ORDER BY 的欄位

• 多表關聯的外鍵欄位（JOIN ON dept_id）。
• 頻繁篩選的旗標欄位（status = 'ACTIVE'），若非 NULL 值佔比低可考慮部分索引（Function-based Index: CASE WHEN status='ACTIVE' THEN id END）。

避免索引的反模式

• 大量 NULL 且鮮少查詢非 NULL 值的欄位。
• 頻繁異動的欄位（如 last_update_time），索引維護成本極高。
• 低選擇性欄位單獨建索引（性別、布林旗標），除非組合索引前導欄位。

動態調整：業務演進定期審視

套裝軟體上線初期的索引設計，隨資料量增長、查詢模式變更可能失效。建議每半年審視 V$OBJECT_USAGE（監控索引使用率）、DBA_HIST_SQLSTAT（高負載 SQL），廢棄未用索引、補齊缺漏索引。

三、把握「查詢 vs 寫入」平衡點，控制單表索引數量

決策矩陣

實務操作建議

1. 查詢 USER_INDEXES、USER_IND_COLUMNS 盤點現有索引。
2. 開啟 ALTER INDEX ... MONITORING USAGE 觀察 1-2 週實際使用情況。
3. 針對未被使用且維護成本高的索引執行 DROP 或 UNUSABLE。
4. 新增索引前必做 EXPLAIN PLAN 對比，上線後持續監控。

總結

索引優化是動態平衡的藝術。遵循「適當的表、適當的欄位、適當的數量」，輔以定期監控與業務驅動的調整，才能讓索引真正成為效能加速器而非負擔。記住：最好的索引，是被精準使用的索引。