Oracle 優化器深度解析：RBO、CBO 與執行計劃選擇策略

前言

在 Oracle 資料庫管理中，優化器是決定 SQL 執行效率的核心組件。本文將系統介紹 Oracle 優化器的兩種優化方式（RBO 與 CBO）、四種優化模式，並深入解析為何有索引卻不走索引的常見問題，幫助 DBA 與開發人員掌握執行計劃分析的核心技能。

一、優化器的兩種優化方式

1. 基於規則的優化（RBO，Rule-Based Optimization）

RBO 遵循 Oracle 內部預定的固定規則集。例如：WHERE 子句中有索引列時優先走索引、等值連接優於範圍掃描等。RBO 不考慮數據分佈、表大小等統計信息，僅依靠規則優先級判斷執行路徑。Oracle 10g 後已不再推薦使用 RBO。

2. 基於成本的優化（CBO，Cost-Based Optimization）

CBO 是 Oracle 8i 及後續版本的主流優化模式。它根據表、索引、列的統計信息（行數、區塊數、異值個數、直方圖等），計算各可能執行計劃的「成本」（CPU、I/O、內存消耗），選擇成本最低者。統計信息由 ANALYZE 或 DBMS_STATS 收集，若統計信息過期或缺失，CBO 可能產生錯誤計劃。

關鍵觀念：不一定走索引才是最優。例如一張僅有兩行數據的小表，全表掃描只需 1 次 I/O，而走索引需 2 次 I/O（索引塊 + 表塊），此時 Full Table Scan 反而更優。

二、四種優化模式詳解

三、優化模式的三層設定機制

1. 實例級

修改 init.ora 或 spfile：

OPTIMIZER_MODE = ALL_ROWS  -- 或 FIRST_ROWS, CHOOSE, RULE

未設定時預設為 CHOOSE。

2. 會話級

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

3. 語句級

使用 Hint 強制指定：

SELECT /*+ FIRST_ROWS */ col1, col2 FROM tbl WHERE id = :b1;

四、為何有索引卻不走索引？

常見原因

• 優化模式為 ALL_ROWS：傾向全表掃描以追求整體吞吐量。
• 統計信息缺失或過期：CBO 缺乏決策依據，可能誤判成本。
• 表極小：優化器判斷全表掃描比索引範圍掃描更划算。
• 索引列隱式轉換：WHERE 子句對索引列使用函數或類型不匹配導致索引失效。
• 選擇度過低：返回行數超過表總行數的一定比例（通常 5%-10%），優化器認為全表掃描更優。

解決策略

1. 收集/更新統計信息：DBMS_STATS.GATHER_TABLE_STATS（推薦）或 ANALYZE TABLE ... COMPUTE STATISTICS。
2. 若確需走索引，使用 Hint：/*+ INDEX(tbl idx_name) */。
3. 調整優化模式至 FIRST_ROWS 或 CHOOSE（視業務而定）。
4. 檢查 SQL 寫法，避免索引列參與運算或函數調用。

五、實用查詢腳本

檢查表/索引統計信息

SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name = 'YOUR_TABLE';
SELECT index_name, num_rows, last_analyzed FROM user_indexes WHERE table_name = 'YOUR_TABLE';

收集統計信息（推薦 DBMS_STATS）

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMP', cascade => TRUE);
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT', indname => 'PK_EMP');