您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle SQL 效能優化 34 條黃金法則：從寫法習慣到執行計畫調校

編輯：Oracle 資料庫相關知識

SQL 優化是資料庫效能提升投資報酬率最高的手段。本文彙整 34 條實戰優化法則，涵蓋表連接順序、WHERE 條件排列、索引運用、Hint 應用、統計資訊維護等核心領域。

優化法則速查表

#	法則	核心說明
1	表連接順序（RBO）	FROM 右至左解析，小表/交叉表置右（驅動表）
2	WHERE 連接條件前置	表連接條件寫在過濾條件之前
3	避免 SELECT *	解析轉換列名耗時，明確列出所需欄位
4	減少資料庫往返	批次處理、Array Fetch、PL/SQL 批量綁定
5	調大 ARRAYSIZE	SQLPlus/ProC 設 200 減少網路交互
6	用 DECODE/CASE 減少掃描	單次掃描完成多條件分支邏輯
7	合併無關查詢	多個簡單查詢合為一條（UNION ALL/條件聚合）
8	刪除重複用 ROWID	`DELETE WHERE ROWID > (SELECT MIN(ROWID) ...)`
9	TRUNCATE 替代 DELETE	全表清空不產生 UNDO、釋放 High Water Mark
10	頻繁 COMMIT	釋放鎖、UNDO、Redo Log Buffer 空間
11	WHERE 替代 HAVING	先過濾再聚合，避免全表掃描後再篩選
12	減少子查詢表存取	子查詢去關聯、合併重複子查詢
13	善用內建函數	如 `NVL`, `DECODE`, `CASE` 替代複雜邏輯
14	使用表別名	減少解析時間、避免列名歧義
15	EXISTS 替代 IN/NOT IN	半連接語意，早期終止，避免子查詢排序合併
16	識別低效 SQL	查詢 `V$SQLAREA` 找高 Buffer Gets、低 Hit Ratio 語句
17	建立/維護索引	B-Tree 結構，大表效果明顯，定期 `REBUILD`
18	EXISTS 替代 DISTINCT	一對多查詢去重，EXISTS 早期終止更快
19	SQL 大寫	Oracle 內部轉大寫，省解析 CPU
20	Java 少用 + 連接字串	用 `StringBuilder` 或參數化
21	避免索引列用 NOT	`NOT` 導致全表掃描
22	避免索引列運算	`WHERE sal*12 > 25000` → `WHERE sal > 25000/12`
23	>= 替代 >	直接定位邊界值，少一次掃描
24	UNION 替代 OR（索引列）	OR 導致全表掃描，UNION 可用索引
25	IN 替代 OR	可讀性較好，8i 後執行計畫通常相同
26	避免索引列 IS NULL	NULL 不存入 B-Tree 索引，改用 `>=0` 等
27	複合索引必走前導列	WHERE 僅引用非前導列 → 全表掃描
28	UNION ALL 替代 UNION	免排序去重，業務允許重複時優先用
29	WHERE 替代 ORDER BY	利用索引順序天然有序，避免 SORT
30	避免索引列隱性轉型	`WHERE char_col = 123` → `TO_NUMBER(char_col)=123` 失效索引
31	警惕殺索引寫法	`!=`, `\|\|`, `+`, 列間比較皆導致全表掃描
32	大範圍查詢不走索引	超過 30% 筆數全表掃描更快
33	慎用排序操作	DISTINCT/UNION/ORDER BY 觸發 SORT，調大 `SORT_AREA_SIZE`
34	GROUP BY 前置過濾	先 WHERE 再 GROUP BY，減少聚合輸入

進階優化工具

-- 找出高消耗 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;