Oracle 索引三大誤區：不總是最佳、統計資訊關鍵、CF 決定存取路徑

誤區一：有索引就該用索引？

發現 Oracle 有索引卻走 Full Table Scan（FTS），**不代表優化器出錯**。兩種常見情況 FTS 更快：

1. 統計資訊缺失或過舊，導致誤判
2. 依據資料量與區塊數，FTS 代價確實低於 Index Scan

統計資訊的威力

SELECT COUNT(*) FROM mytable;

• 無統計資訊：FTS 讀 6000+ 區塊（8K/塊）
• 收集統計後：INDEX FAST FULL SCAN 僅讀 450 區塊
• 但統計資訊收集不當（如採樣率過低）反而誤導優化器

誤區二：索引一定比表快？理解 CF 與 FF

優化器評估 Index Access Path 代價的核心公式：

Cost ≈ FF × (CF + 索引區塊數)

兩大關鍵指標：

• CF (Clustering Factor)：每讀一個索引區塊，平均需讀幾個資料區塊。CF 越接近表區塊數越好；CF 接近甚至超過表列數則極差。大量 DML 後 CF 惡化，需 ALTER INDEX REBUILD。
• FF (Filtering Factor)：查詢返回列數佔總列數比例。優化器依統計資訊估算。

實戰驗證：相似 SQL、天壤之別

表 mytables 32萬列，PK myid 範圍 1~409654

-- SQL 1：FF ≈ 100%
SELECT * FROM mytables WHERE myid >= 1;
-- Execution Plan: TABLE ACCESS FULL, Cost=3132, 41473 consistent gets

-- SQL 2：FF ≈ 1%
SELECT * FROM mytables WHERE myid >= 400000;
-- Execution Plan: INDEX RANGE SCAN (PK_MYTABLES), Cost=346, 6647 consistent gets

FF 差異導致存取路徑截然不同。**寫 SQL 前估估 FF，幾乎能預判是否走索引**。

誤區三：索引建好就不用管？碎片與重建

• 刪除列時，表空間可重用，**索引空間不會自動重用**，產生碎片
• 頻繁 DELETE 的索引表，需定期 ALTER INDEX REBUILD 或 COALESCE
• 條件允許時，階段性 TRUNCATE TABLE 同時清除表與索引碎片
• 經驗法則：單條 SQL 存取超過 20% 總列數時，索引通常無助於效能

DBA 行動清單

1. 建立統計資訊收集策略（DBMS_STATS、自動任務、採樣率、直方圖）
2. 定期監控 DBA_INDEXES.CLUSTERING_FACTOR，CF 惡化觸發重建
3. SQL 調優時先看 FF 預估，再看 Execution Plan 驗證
4. 建立索引維護窗口，納入變更管理流程

索引不是銀彈，而是需要精準統計資訊、理解 CF/FF 機制、持續維護碎片的精密儀器。掌握這三大核心，才能真正駕馭 Oracle 索引效能。