在 Oracle 數據庫開發與維護過程中，SQL 語句的效率直接決定系統性能優劣。本文整理了 20 條經過實戰驗證的優化原則，涵蓋查詢重寫、索引策略、執行計劃分析等核心領域，助你快速定位瓶頸、提升吞吐量。
一、查詢重寫與條件下推
- 優先用 WHERE 替代 HAVING
HAVING 在分組後過濾，需先聚合再篩選；WHERE 可在讀取階段直接過濾，大幅減少中間結果集。
- 多表連接時，小表放右側（或驅動表）
Oracle 嵌套循環連接以驅動表為外層，小表驅動大表可減少內層探測次數。
- 減少對表的重複訪問
合併子查詢、使用 WITH 子句（CTE）或物化視圖，避免多次全表掃描。
- WHERE 子查詢前置
將過濾性最強的子查詢條件前置，讓優化器盡早縮小結果集。
- 禁用 SELECT *
星號會在解析階段展開為所有列，增加網絡傳輸與解析開銷；顯式列出所需列可利用覆蓋索引。
- 批次提交：適度使用 COMMIT
長事務鎖定資源久，易產生「ORA-01555 snapshot too old」；分批提交釋放 Undo，但過度提交增加 Redo 生成，需平衡。
二、進階改寫技巧
- DECODE / CASE 避免重複掃描
單次掃描內完成多條件分支聚合，替代多次 UNION ALL 或自連接。
- 內部函數下推
將計算下推至存儲層（如 Exadata Smart Scan），減少數據傳輸量。
- 多表查詢必加別名並前綴列名
消除列名歧義，助優化器精準匹配索引與統計信息。
- EXISTS 替代 IN
IN 子查詢常轉為半連接，但 EXISTS 可在滿足第一行時立即返回，配合相關子查詢更高效。
- NOT EXISTS 替代 NOT IN
NOT IN 若子查詢含 NULL 則無行返回，且常觸發全表掃描 + 排序合併；NOT EXISTS 走反向半連接，性能穩定。
- 表連接優於 EXISTS（特定場景）
當需返回駕駛表非鍵值列時，直接 JOIN 避免再次查表；若僅判斷存在性，EXISTS 更優。
- EXISTS 替換 DISTINCT 去重
一對多關聯查詢主表時，DISTINCT 需排序去重；EXISTS 半連接只需探測存在性，省去排序開銷。
反例：
SELECT DISTINCT d.dept_no, d.dept_name FROM dept d, emp e WHERE d.dept_no = e.dept_no;
優化：
SELECT d.dept_no, d.dept_name FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_no = d.dept_no);
三、工具與索引深度解析
- TKPROF 分析執行統計
格式化 Trace 文件，聚焦「Parse/Execute/Fetch」耗時、行數、等待事件，快速定位高消耗 SQL。
- 索引原理與權衡
索引存儲 (Key, ROWID)；查詢列若全在索引中（覆蓋索引），可避免回表。代價：寫入開銷、空間佔用、需定期 ALTER INDEX idx REBUILD 消除碎片。
- INDEX RANGE SCAN 適用場景
① 範圍條件 LIKE 'M%'（前綴匹配）；② 非唯一索引等值查詢 WHERE manager = 'LI'。
失效陷阱：LIKE '%LI' 導致首字符通配，優化器放棄索引轉全表掃描。
- 驅動表選擇策略
CBO：依統計信息估算成本選最小代價表；RBO：若所有連接列均有索引，FROM 最後的表為驅動表。
例：FROM worker w, lodging l WHERE w.lodging = l.lodging，LODGING 有索引而 WORKER 無，WORKER 成為驅動表。
- 多索引合併（AND-EQUAL）
多個同等級索引（如兩非唯一索引）可合併 ROWID 取交集。優先級：唯一索引 > 非唯一；常量比較 > 列間比較；FROM 後表索引優先；WHERE 先出現索引優先。
- 等值 vs 範圍比較優先級
非唯一索引：等值比較優先於範圍；唯一索引範圍比較等級低於非唯一索引等值比較。
例：WHERE dept_no > 20 AND emp_cat = 'A'，優化器選 EMP_CAT 索引，再過濾 DEPT_NO。
- 強制索引失效技巧
當多索引等級相同但選擇度差異大時，對低選擇度索引列加 +0 或 ||'' 破壞索引列純度，迫使優化器選擇高選擇度索引，避免排序合併開銷。
掌握上述原則並結合 EXPLAIN PLAN、SQL_TRACE、AWR/Statspack 實測，方能在生產環境中游刃有餘地解決 Oracle SQL 性能疑難雜症。
