Oracle 為何選擇索引還是全表掃描？優化器決策深度解析

SQL 執行計劃生成的 9 大步驟

1. 語法分析：檢查語法規範性。
2. 語義分析：驗證對象存在性與權限。
3. 視圖轉換：展開視圖為基表查詢。
4. 表達式轉換：簡化複雜表達式。
5. 選擇優化器：RBO vs CBO，決定後續路徑。
6. 選擇連接方式：Nested Loop / Hash Join / Sort Merge Join。
7. 選擇連接順序：驅動表與被驅動表排序。
8. 選擇數據搜索路徑：全表掃描 vs 索引訪問 vs 其他。
9. 執行計劃運行。

兩代優化器核心差異

默認 OPTIMIZER_MODE=CHOOSE：有統計信息用 CBO，否則退回 RBO。

索引未被使用的 12 大排查清單

1. 優化器模式：確認 OPTIMIZER_MODE 非 RULE。
2. WHERE 條件覆蓋：索引列（或組合索引首列）必須出現在 WHERE 中。
3. 連接方式：只有 Nested Loop 能有效利用內表索引；Hash Join/Sort Merge Join 對索引利用極低。
4. 連接順序：驅動表全表掃描時，其索引無法用於連接過濾。
5. 字典表/視圖：未分析字典對象導致劣質計劃，**切勿自行分析字典表**（致死鎖/性能退化）。
6. 索引列做函數參數：WHERE UPPER(col)=... 導致索引失效，改用函數索引。
7. 隱式類型轉換：char_col = 123 觸發 TO_NUMBER，同第 6 點。
8. 統計信息陈舊：高變更率表需定期 ANALYZE TABLE ... COMPUTE STATISTICS FOR ALL INDEXES 或 DBMS_STATS。
9. 索引選擇性低：唯一值少（如性別、狀態碼），CBO 認為全表掃描更優。可建直方圖（METHOD_OPT=>'FOR COLUMNS SIZE 254 col'）輔助決策。
10. 可空列：COUNT(*)、WHERE col IS NULL 不走索引（全空鍵值不存入 B*Tree）。
11. 並行查詢 (PQO)：並行執行計劃不使用索引。
12. 綁定變量窺探：非等值條件（<, >, LIKE）配合綁定變量可能導致計劃固化不佳。

強制指定執行計劃：HINT 語法

SELECT /*+ INDEX(a idx_col1) */ * FROM a WHERE col1 = :val;

注意：/*+ 緊跟 SELECT，無空格；否則視為普通註釋。

兩大跟蹤調試利器

1. EXPLAIN PLAN + AUTOTRACE

-- 建表腳本：$ORACLE_HOME/rdbms/admin/utlxplan.sql
SET AUTOTRACE ON
SELECT ...;  -- 直接顯示計劃、Cost、連接方式、邏輯/物理讀

2. TKPROF 全會話跟蹤

-- 1. 從 V$SESSION 獲取目標 SID, SERIAL#
-- 2. SYS 執行開啟跟蹤
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE);
-- 3. 運行業務，生成 user_dump_dest 下的 ora_.trc
-- 4. 關閉跟蹤
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, FALSE);
-- 5. 分析
tkprof ora_.trc output.txt explain=user/pwd

輸出文件含每條 SQL 的計劃、CPU、邏輯讀、物理讀、耗時，快速定位性能瓶頸。