為何 Oracle 有時不使用索引？執行計畫九步驟與優化器抉擇邏輯

SQL 執行九大步驟

1. 語法分析
2. 語義分析 (物件存在性、權限)
3. 視圖轉換 (View Merging)
4. 表達式轉換 (複雜轉簡單等效)
5. 選擇優化器 (RBO/CBO)
6. 選擇聯結方式 (NL/SMJ/HJ)
7. 選擇聯結順序 (驅動表)
8. 選擇存取路徑 (全表掃描/索引/叢集…)
9. 執行計畫

兩大優化器

參數 OPTIMIZER_MODE：CHOOSE (預設) = 有統計用 CBO、無統計用 RBO；RULE 強制 RBO。

索引未被使用五大排查步驟

1. 確認優化器模式：SHOW PARAMETER OPTIMIZER_MODE。若為 RBO 或無統計走 RBO，索引使用規則極嚴格。
2. 檢查 WHERE 條件：被索引欄 (或組合索引引導欄) 是否出現在 WHERE 子句？否則索引無法使用。
3. 檢視聯結方式：
   • Nested Loop (NL)：唯一能有效利用內表索引。
   • Sort Merge Join (SMJ)：索引僅能省略排序。
   • Hash Join (HJ)：索引幾乎無幫助。
4. 檢視聯結順序：外表 (驅動表) 存取為全表掃描，其索引欄位即使存在也用不上 (如 EMP.DEPTNO=DEPT.DEPTNO，EMP 為外表時 DEPTNO 索引無效)。
5. 是否涉及字典表/視圖：字典表未收集統計，CBO 估算極差；嚴禁自行 ANALYZE 字典表 (恐導致死鎖/效能崩潰)。

實務建議

• 全庫啟用 CBO，定期 DBMS_STATS.GATHER_SCHEMA_STATS (含直方圖)。
• 關鍵 SQL 使用 EXPLAIN PLAN / DBMS_XPLAN.DISPLAY_CURSOR 對比 E-Rows vs A-Rows 找估算偏差。
• 必要時用 Hint (/*+ INDEX(t idx) */) 或 SQL Plan Baseline 固定計畫。