Oracle SQL 性能優化 34 條鐵律：從執行計劃到索引重構全景指南

Oracle SQL 性能優化 34 條鐵律：從執行計劃到索引重構全景指南

基於規則優化器（RBO）與成本優化器（CBO）雙視角，匯總 34 條 Oracle SQL 調優鐵律，涵蓋表連接順序、WHERE 子句排序、索引失效模式、子查詢重寫、排序消除、分頁優化等核心場景，附帶 V$SQLAREA 慢 SQL 發現腳本與索引維護最佳實踐。

核心優化原則

1. FROM 子句表順序（RBO）：右到左解析，記錄最少的表放最後（驅動表）。3 表以上選擇交叉表。
2. WHERE 條件順序：自下而上解析，表連接條件寫前，過濾性最強條件寫最後。
3. 避免 SELECT *：轉換列名需查數據字典，增加解析開銷。
4. 減少資料庫訪問次數：批量綁定、陣列提交、ARRAYSIZE=200。
5. 用 DECODE/CASE 替換多次掃描：單次掃描完成多條件聚合。
6. 整合無關簡單查詢：聯合為單一 SQL 減少解析/網絡開銷。

高頻重寫模式

索引優化鐵律

• 組合索引必須遵循「最左前綴」，WHERE 引用首列才生效。
• 定期 ALTER INDEX idx REBUILD TABLESPACE ts; 消除碎片。
• UNION ALL 替代 UNION 省去排序去重，業務允許重複時首選。
• ORDER BY 僅在列全在同一索引且順序一致、且非空時用索引排序。
• 隱式類型轉換發生在索引列上會導致失效：WHERE char_col = 123 → TO_NUMBER(char_col)=123 失效，改 WHERE char_col = '123'。

慢 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;

GROUP BY / DISTINCT 優化

• 先 WHERE 過濾再 GROUP BY，減少分組輸入行數。
• 關聯查詢分組優先用主鍵/唯一鍵列，避免非依賴列導致 ONLY_FULL_GROUP_BY 報錯。
• MIN/MAX 優化：SELECT MIN(id) FROM t → SELECT id FROM t ORDER BY id LIMIT 1 走索引。

分頁優化

-- 延遲關聯法：先取主鑰再回表
SELECT a.* FROM big_table a
JOIN (SELECT id FROM big_table WHERE cond ORDER BY id LIMIT 10000,20) b USING (id);

-- 已知邊界值改範圍掃描
SELECT * FROM t WHERE id > 10000 ORDER BY id LIMIT 20;

子查詢優化

• IN (SELECT ...) → EXISTS / JOIN
• 標量子查詢移至 SELECT 列表或改 OUTER APPLY（12c+）
• 物化視圖 / WITH 子句（Subquery Factoring）複用中間結果