萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 索引三大誤區深度解析:索引非萬能、CF/FF 成本模型、統計資訊決定生死

誤區一:有索引就一定快

Oracle 優化器基於成本 (CBO) 決定存取路徑。以下情況會選擇 **全表掃描 (FTS)** 而非索引:

  1. 無統計資訊或統計過期 → 誤判選擇率。
  2. 實際上 FTS 成本 < 索引掃描成本 (大量資料、低選擇率、聚簇因子差)。

核心成本模型:CF (Clustering Factor) 與 FF (Filtering Factor)

  • CF:索引順序與表資料順序的一致程度。
    CF ≈ 掃描索引葉塊數 + 對應表塊隨機讀取數。
    理想 CF ≈ 表塊數;最差 CF ≈ 表行數 (每行跳躍不同塊)。
    大量 DML 後 CF 惡化 → 需 ALTER INDEX ... REBUILD 或 COALESCE。
  • FF:查詢返回行數 / 總行數。
    優化器依統計資訊估算。

索引存取成本 ≈ FF × (CF + 索引葉塊數)。若 > 表塊數 → 選 FTS。

實測案例

表 MYTABLES 32.6 萬行,主鍵 MYID (1~409654)。

-- FF ≈ 100% → 選 FTS
SELECT * FROM MYTABLES WHERE MYID >= 1;
-- Plan: TABLE ACCESS FULL, Cost=3132, 41473 consistent gets

-- FF ≈ 1% → 選 INDEX RANGE SCAN
SELECT * FROM MYTABLES WHERE MYID >= 400000;
-- Plan: INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID, Cost=低

誤區二:統計資訊收集一次就夠了

  • 大量 DML 後必須 DBMS_STATS.GATHER_TABLE_STATS (含 CASCADE=>TRUE 同步索引統計)。
  • 直方圖 (METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO') 對傾斜欄位至關重要。
  • 鎖定統計 (LOCK_TABLE_STATS) 防止夜間自動收集導致計畫突變。

誤區三:索引越多越好

  • 每個索引增加 DML 開銷 (INSERT/UPDATE/DELETE 需同步維護所有索引)。
  • 未使用的索引佔用空間、增加備份時間、拖慢統計收集。
  • 定期查 DBA_HIST_SQL_PLAN / V$OBJECT_USAGE (監控模式) 識別廢棄索引並 DROP。

實戰檢查清單

  1. 關鍵表每日/每週收集統計 (含直方圖)。
  2. 監控 CLUSTERING_FACTOR 趨勢,定期 Rebuild 高 CF 索引。
  3. SQL Tuning Advisor / SQL Plan Baseline 固化優良計畫。
  4. 分區表使用 Local Index 降低維護成本,Global Index 僅用於跨分區唯一性約束。
copyright © 萬盛學電腦網 all rights reserved