Oracle SQL 語句優化 30 大核心規則深度解析

前言

在 Oracle 資料庫效能調校中，SQL 語句優化佔據核心地位。本文系統整理 30 條實戰驗證的優化準則，涵蓋優化器選擇、存取路徑、SQL 重寫、索引策略、執行計畫分析等關鍵領域，助力 DBA 與開發人員構建高效能的資料庫應用。

一、優化器選擇與模式設定

1. 精準選擇優化器模式

Oracle 提供三種優化器：RULE（基於規則）、COST（基於成本，CBO）與 CHOOSE（選擇性）。透過 init.ora 中的 OPTIMIZER_MODE 參數（可設為 RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS）或 Session/Statement 層級的 Hint 進行覆蓋。建議明確指定 CBO 或 RBO，避免使用預設 CHOOSE 導致非預期的全表掃描。 啟用 CBO 時，務必定期執行 ANALYZE 或 DBMS_STATS 收集物件統計資訊，確保執行計畫準確。

二、資料存取路徑優化

2. 善用 ROWID 避免全表掃描

Oracle 以 Full Table Scan（多區塊讀取優化）與 ROWID 存取兩種方式讀取資料。ROWID 記錄物理位置，索引提供快速 ROWID 查找路徑。優先設計覆蓋索引，讓查詢直接從索引取得所需欄位，省略 Table Access by ROWID 步驟。

三、共享游標與解析優化

3. 強化 SQL 共享機制

Oracle 將解析後的 SQL 存放於 SGA Shared Pool，實現游標共享。共享需滿足三大條件：字元級完全相同（含空白、換行）、參考物件完全一致（含 Synonym 解析結果）、綁定變數名稱一致。 適當調大 SHARED_POOL_SIZE 提高命中率；對於非共享的臨時 SQL，考慮使用 CURSOR_SHARING=SIMILAR/FORCE（需評估副作用）。

四、多表連接順序與條件排序

4. RBO 下的驅動表選擇

RBO 解析 FROM 子句時從右至左處理，最後列出的表為驅動表。原則：選擇回傳行數最少的表作為驅動表；三表以上連接時，選擇被其他表引用最多的「交叉表」作為驅動表。

5. WHERE 子句條件順序

Oracle 自下而上解析 WHERE 條件。連接條件優先，過濾性最強的條件置於末尾，讓優化器盡早縮小結果集。

五、SQL 寫法最佳實踐

6. 禁用 SELECT *

* 需查詢資料字典轉換為完整欄位清單，增加解析開銷。明確列出所需欄位，亦利於覆蓋索引生效。

7. 減少資料庫往返次數

每次 SQL 執行伴隨解析、綁定、I/O 等開銷。採用批次綁定、陣列提取（ARRAYSIZE 建議 200）、單一 SQL 完成多筆資料查詢/更新，大幅降低網路與 CPU 消耗。

8. 巧用 DECODE/CASE 聚合邏輯

將多條相似查詢合併為單一掃描，配合 DECODE 或 CASE 在同一次掃描中完成分組統計，避免重複存取。

9. 整合無關聯的簡單查詢

利用 DUAL 與外部連接將多個獨立查詢合併為一個，減少 Parse/Execute 開銷（權衡可讀性）。

10. 高效刪除重複資料

DELETE FROM emp e
 WHERE e.rowid > (SELECT MIN(x.rowid) FROM emp x WHERE x.emp_no = e.emp_no);

利用 ROWID 唯一性，單次掃描完成去重。

11. TRUNCATE 替代 DELETE

清空整表時，TRUNCATE（DDL）不產生 UNDO、不觸發 Trigger、瞬間完成、釋放 High Water Mark。不可回滾，謹慎使用。

12. 適度提交釋放資源

頻繁 COMMIT 釋放 Undo Segment、鎖、Redo Log Buffer 空間，但需平衡交易完整性與效能。

13. 計數函數選擇

COUNT(*)、COUNT(1)、COUNT(pk_col) 在現代版本效能差異極小；有索引時優先 COUNT(index_col)。

14. WHERE 替代 HAVING

HAVING 在 GROUP BY 後過濾，需排序/聚合完整結果集。非聚合條件下推至 WHERE，縮小參與聚合的資料量。

15. 減少子查詢對表的重複存取

合併相關子查詢、使用多欄位子查詢、UPDATE 時採用集合賦值語法：

UPDATE emp SET (emp_cat, sal_range) = 
  (SELECT MAX(category), MAX(sal_range) FROM emp_categories)
 WHERE emp_dept = 20;

六、進階優化技巧

16. 以 PL/SQL 函數封裝查詢邏輯

將複雜連接轉為函數查找，減少主查詢連接開銷，適合維度表查找場景。

17. 善用表別名

多表查詢強制加上別名並前綴所有欄位，縮短解析時間、消除欄位歧義。

18. EXISTS 替代 IN

半連接語意，滿足即返回，避免子查詢完整物化，尤其適合大驅動表小查詢表場景。

19. NOT EXISTS 替代 NOT IN

NOT IN 含 NULL 時語意陷阱且易全表掃描；NOT EXISTS 或 Outer Join + IS NULL 效能更穩定。

20. 連接替代 EXISTS

當子查詢無重複鍵值時，Inner Join 效能通常優於 EXISTS（RBO 下為 NESTED LOOP vs FILTER）。

21. EXISTS 替代 DISTINCT

一對多查詢去重時，EXISTS 可在首次匹配後停止掃描，避免排序去重開銷。

七、效能診斷與工具

22. 識別低效 SQL

SELECT executions, disk_reads, buffer_gets,
       ROUND((buffer_gets-disk_reads)/buffer_gets,2) hit_ratio,
       ROUND(disk_reads/executions,2) reads_per_run, sql_text
  FROM v$sqlarea
 WHERE executions>0 AND buffer_gets>0
   AND (buffer_gets-disk_reads)/buffer_gets < 0.8
 ORDER BY 4 DESC;

23. 善用 TKPROF 分析 Trace

Session 級啟用：ALTER SESSION SET SQL_TRACE=TRUE；Instance 級需 TIMED_STATISTICS=TRUE 與 USER_DUMP_DEST。Trace 檔經 TKPROF 格式化後分析 Parse/Execute/Fetch 統計。

24. EXPLAIN PLAN 與 AUTOTRACE

掌握「由內而外、由上而下」閱讀規則；NESTED LOOPS 需先看驅動端操作。SET AUTOTRACE TRACEONLY 即時查看執行計畫與統計。

八、索引策略深度解析

25. 索引建立與維護原則

B-Tree 結構加速查詢與連接，支援 PK/UK 約束。代價：DML 額外 4-5 次 I/O 維護索引、佔用儲存空間。 定期 ALTER INDEX REBUILD 消除碎片；移除未使用索引。

26. 索引存取路徑

• INDEX UNIQUE SCAN：等值查詢唯一索引，單一 ROWID。
• INDEX RANGE SCAN：範圍查詢或非唯一索引。
• 覆蓋索引：所需欄位全在索引中，省略 Table Access。
• 前導萬用字元（LIKE '%abc'）導致索引失效，改用反向索引或全文檢索。

27. 驅動表選擇機制

• CBO：依統計資訊估算成本選擇。
• RBO：有索引連接鍵時，FROM 最後一張表為驅動表；無索引時，無連接鍵索引的表優先。

28. 多索引合併（AND-EQUAL）

多個同等級索引（通常為非唯一）可合併掃描再交集。優先序：唯一>非唯一；同等級看 FROM 順序（最後優先）或 WHERE 順序（最前優先）。

29. 等值 vs 範圍比較

WHERE 同時含等值與範圍條件時，等值索引優先，範圍條件事後過濾；無法合併時僅使用等值索引。

30. 索引優先序模糊時的決策

優化器無法判斷高低時，採用 WHERE 子句中最先出現的索引條件。實務建議：收集完整統計資訊、使用 Hint 引導、改寫 SQL 使優先序明確。

總結

SQL 優化是系統工程，需結合業務場景、資料分布、Oracle 版本特性綜合施策。本文 30 條規則涵蓋優化器、存取路徑、寫法重構、索引設計、診斷工具五大維度，建議在開發規範、Code Review、效能測試階段系統性應用，持續迭代優化。