凝聚 Oracle 社群數十年實戰經驗的 34 條調優鐵律，涵蓋 SQL 寫法、索引策略、統計資訊、架構決策，每條附說明與對比範例，適合開發、DBA 案頭常備。
SQL 寫法優化 (1-7)
- 表連接順序：RBO 下 FROM 右至左解析，駕駛表（最後）應為最小結果集；3+ 表連接選交叉表為駕駛表。
- WHERE 條件順序：自下而上解析，表連接條件先寫，過濾率最高的條件寫在最後。
- 避免 SELECT *：解析階段需查詢資料字典展開欄位，增加開銷。
- 減少資料庫往返：批次處理、Array Fetch、PL/SQL 批次綁定。
- 調大 ARRAYSIZE：SQL*Plus / Pro*C 設 200 減少網路封包。
- 用 DECODE/CASE 替換多次掃描：單次掃描完成多條件分支。
- 合併無關查詢：多個獨立查詢合併為單一 SQL（UNION ALL 或多欄位回傳）。
DML 優化 (8-10)
- 刪除重複用 ROWID：
DELETE FROM t WHERE ROWID > (SELECT MIN(ROWID) FROM t x WHERE x.key = t.key);
- 全表清空用 TRUNCATE：DDL、不產生 UNDO、瞬間完成、釋放 High Water Mark（不可回滾）。
- 頻繁 COMMIT：釋放 UNDO、鎖、Redo Log Buffer 空間，降低爭用。
查詢改寫 (11-18)
- WHERE 替代 HAVING：HAVING 在分組後過濾，WHERE 先過濾再分組，減少排序/聚合成本。
- 減少子查詢掃描：將重複子查詢提升為 JOIN 或 WITH 子句。
- 善用內建函數：複雜邏輯用 SQL 函數替代多次掃描。
- 使用表別名：多表查詢必加別名並前綴欄位，減少解析時間與歧義。
- EXISTS 替代 IN：
EXISTS 符合即停止，IN 需完整掃描子查詢結果；NOT IN 最差（全表掃描+排序），改 NOT EXISTS 或 Outer Join。
- 識別低效 SQL：查詢
V$SQLAREA 依 BUFFER_GETS/EXECUTIONS、Hit Ratio 找出前 10 消耗語句。
- 索引提升效率：B-tree 結構，大表篩選率 < 10% 時效果明顯；代價：DML 額外 4-5 次 I/O 維護索引。
- 定期重建索引：
ALTER INDEX idx REBUILD [ONLINE] 消除碎片。
進階 SQL 技巧 (19-28)
- EXISTS 替代 DISTINCT：一對多查詢去重用 EXISTS，避免排序。
- SQL 大寫：Oracle 內部轉大寫再解析，小寫多一次 CPU。
- Java 少用 + 串聯 SQL：用
StringBuilder 或 PreparedStatement。
- 索引欄避免 NOT：
NOT 導致全表掃描，改寫正向條件。
- 索引欄避免運算/函數：
WHERE sal*12 > 25000 → WHERE sal > 25000/12。
- >= 替代 >：
deptno >= 4 直接定位到 4，> 3 先定位 3 再掃描。
- UNION 替代 OR (索引欄)：
OR 導致全表掃描，UNION 可用多索引合併。
- IN 替代 OR (同欄多值)：
col IN (10,20,30) 可走索引範圍掃描。
- 索引欄避免 IS NULL/IS NOT NULL：NULL 不存入 B-tree，改
col >= 0 (數值) 或 col IS NOT NULL 配合函數索引。
- 複合索引必須用前導欄：
INDEX (a,b) 僅 WHERE a=... 或 WHERE a=... AND b=... 會用到。
集合運算與排序 (29-34)
- UNION ALL 替代 UNION：免排序去重，若業務允許重複或已知無重複必用 ALL。
- WHERE 替代 ORDER BY：
ORDER BY 需索引全匹配且非空，改用 WHERE col > 0 利用索引順序。
- 避免隱性型別轉換：
WHERE char_col = 123 → TO_NUMBER(char_col)=123 導致索引失效，寫 WHERE char_col = '123'。
- 警惕索引失效寫法：
!=, ||, +, 同欄互比 皆導致全表掃描。
- 大表全掃描不可怕：取回 > 30% 列時全掃描反而更快；索引通常快數倍至數千倍。
- 優化 GROUP BY：先
WHERE 過濾再 GROUP BY，減少分組輸入行數。
索引失效快速自查清單
[ ] WHERE 欄位用函數/運算 (UPPER, TRUNC, +, *)
[ ] WHERE 欄位用 NOT, !=, IS NULL
[ ] 隱性型別轉換 (數字比字串)
[ ] 複合索引未用前導欄
[ ] LIKE '%abc%' 導向掃描
[ ] OR 連接多索引欄 (改 UNION)
[ ] 統計資訊過期 (DBMS_STATS.GATHER_TABLE_STATS)
架構層面建議
- 分區表配合 Partition Pruning 實現「大表小查」。
- Materialized View 預聚合高頻報表查詢。
- Result Cache (11g+) 緩存不常變更的查詢結果。
- SQL Plan Baseline / SPM 鎖定穩定執行計畫，防止統計資訊波動導致計畫退化。
