Oracle 優化器是 SQL 執行計势生成的大腦。理解三大優化器模式 (RBO、CBO、CHOOSE) 的運作機制、適用場景、遷移路徑，以及表訪問路徑 (全表掃描 vs 索引掃描) 的選擇邏輯，是 SQL 調優的前提知識。本文系統梳理核心概念，並說明為什麼現代 Oracle **只推薦 CBO (COST)**。
| 模式 | 全稱 | 決策依據 | 特點 | 適用版本/場景 |
|---|---|---|---|---|
| RULE | Rule-Based Optimizer (RBO) | 固定規則排名 (如：唯一索引 > 非唯一索引 > 全表掃描) | 不依賴統計信息、確定性強、無法感知數據分布/量級變化、不支援新特性 (分區、並行、Bitmap、Hash Join 等) | Oracle 7/8i 主流，**10g+ 已廢棄**，僅極舊系統維護 |
| COST | Cost-Based Optimizer (CBO) | 統計信息 (表/索引/列/系統) + 代價模型 (CPU/IO/網路/內存) | 動態適應數據變化、支援所有新特性、基數估算核心、可透過 Hint/統計信息干預 | **9i+ 默認、10g+ 唯一推薦**、所有生產環境 |
| CHOOSE | Choose Optimizer | 有統計信息 → CBO；無統計信息 → RBO | 過渡期產物，**10g+ 已廢棄**，行為不可預測 (統計信息失效瞬間退化為 RBO) | 8i/9i 遷移過渡期 |
-- 實例級 (init.ora / spfile)
OPTIMIZER_MODE = ALL_ROWS | FIRST_ROWS_n | FIRST_ROWS -- CBO 目標模式
-- OPTIMIZER_MODE = RULE | CHOOSE -- 已廢棄參數值
-- 會話級
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
-- SQL 級 Hint
/*+ ALL_ROWS */ /*+ FIRST_ROWS(10) */ /*+ RULE */ /*+ CHOOSE */關鍵點：OPTIMIZER_MODE 僅設定 CBO 的**目標模式** (吞吐量 vs 響應時間)，**不再控制 RBO/CHOOSE 選擇**。10g+ 只能運行 CBO。
CBO 依賴 DBMS_STATS 收集的統計信息：
維護鐵律：定期收集 (每日/每週)、代表性採樣 (AUTO_SAMPLE_SIZE)、關鍵列直方圖 (SIZE AUTO/SKEWONLY)、鎖定核心表統計信息 (LOCK_TABLE_STATS) 防波動、發布前驗證 (PENDING STATS)**。
DB_FILE_MULTIBLOCK_READ_COUNT，默認 128/1MB)，順序掃描高水位線以下所有塊。_SMALL_TABLE_THRESHOLD)。Cost = (Blocks / MBRC) * MREADTIM / SREADTIM (系統統計信息模式) 或簡化公式。| 掃描類型 | 觸發條件 | 代價特徵 | 回表 (TABLE ACCESS BY INDEX ROWID) |
|---|---|---|---|
| INDEX UNIQUE SCAN | 等值條件命中唯一索引/主鍵 (WHERE pk = :val) | 極低 (BLEVEL + 1)，精確 1 行 | 需回表取非索引列；若查詢列全在索引中 (覆蓋索引) 則無回表 |
| INDEX RANGE SCAN | 範圍條件 (>, <, >=, <=, LIKE 'abc%') 或非唯一索引等值 | BLEVEL + 葉塊掃描比例 + 回表代價 | 通常需回表，聚簇因子決定回表 IO 散度 |
| INDEX FULL SCAN | 無條件或非前導列條件，需有序結果 | 掃描所有葉塊 (順序讀) | 視查詢列是否覆蓋 |
| INDEX FAST FULL SCAN | 無序掃描所有葉塊 (多塊讀)，類似全表掃描但只讀索引 | 多塊讀，無回表 (需覆蓋所有查詢列) | 無回表 |
| INDEX SKIP SCAN | 非前導列有條件，前導列 NDV 極小 (如性別 M/F) | 子索引掃描迭代 | 視查詢列 |
CLUSTERING_FACTOR 衡量索引順序與表物理順序的一致性：
ALTER TABLE ... MOVE 重組表 (配合 ORDER BY pk) 降低聚簇因子，但會導致索引 UNUSABLE 需重建。| 模式 | 示例 | 原因 | 修正 |
|---|---|---|---|
| 函數/運算作用於索引列 | WHERE UPPER(name) = 'ABC' | 索引存原始值，無法匹配 | WHERE name = 'ABC' 或函數索引 CREATE INDEX idx_upper_name ON t(UPPER(name)) |
| 隱式類型轉換 | WHERE varchar_col = 123 | TO_NUMBER(varchar_col) = 123 導致索引失效 | WHERE varchar_col = '123' |
| 前導列未用 / 通配符開頭 | WHERE name LIKE '%abc' | B*Tree 索引無法支援前導通配符 | 反向索引 REVERSE、文本索引、或應用層邏輯變更 |
| NOT IN / NOT EXISTS 子查詢 | WHERE id NOT IN (SELECT ...) | 語義複雜、空值陷阱，優化器常轉 Hash Join Anti | NOT EXISTS、LEFT JOIN ... WHERE IS NULL、物化視圖 |
| OR 條件跨列 | WHERE a = 1 OR b = 2 | 難以合併索引 (除非 Bitmap Index / Index Combine) | UNION ALL 改寫、Bitmap Index (DW)、擴展統計 |
EXEC DBMS_STATS.GATHER_DATABASE_STATS(OPTIONS=>'GATHER AUTO');/*+ RULE */、/*+ CHOOSE */、OPTIMIZER_MODE=RULE 會話。逐個測試 CBO 計劃，補索引/統計信息/Hint 修正。_OPTIMIZER_COST_MODEL=CHOOSE 等隱含參數恢復默認。RBO 是「死規則」，CBO 是「活模型」。Oracle 10g 徹底移除 RBO/CHOOSE 代碼路徑，標誌著**統計信息驅動的成本優化時代全面到來**。現代 DBA 的核心職責不再是背誦 RBO 規則排名，而是：**建立統計信息收集自動化體系、解讀 10053 Trace 理解代價模型、識別基數估算偏差根因、應用 SPM (SQL Plan Management) 穩定優質計劃**。掌握 CBO 三板斧：統計信息準、系統統計信息全、代價模型懂，才能駕馭優化器而非被優化器駕馭。
