Oracle SQL 效能優化大全：10 大核心技巧與進階策略

1. 表連接順序（RBO 時代遺規，CBO 參考）

FROM 子句**從右到左**解析，最後寫的表最先處理（驅動表）。多表連接時，**將結果集最小的表放最後**（或用交叉表作為驅動表）。

2. WHERE 條件順序（自下而上解析）

表連接條件**寫在最前**，過濾性最強的條件（過濾掉最多行）**寫在最後**。

3. 避免 SELECT *

* 需查詢字典轉換為所有欄位名，增加解析開銷與網路傳輸。顯式列出需要的欄位。

4. 減少資料庫往返

• 批次處理用 FORALL/BULK COLLECT。
• 應用層調大 ARRAYSIZE（建議 200-500），減少 FETCH 呼叫。

5. DECODE/CASE 替代多次掃描

單次掃描內用 DECODE 或 CASE 完成多條件分支，避免重複掃描同一表或重複 JOIN。

6. 整合無關查詢

多個獨立簡單查詢可用 UNION ALL 或單一游標合併，減少解析與網路開銷。

7. 高效刪除重複資料（ROWID）

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

保留最早插入行（MIN ROWID），其餘刪除。大表用 CTAS 重建更快。

8. TRUNCATE 替代 DELETE（全表清空）

• TRUNCATE：DDL、不產生 UNDO、不觸發 Trigger、釋放 High Water Mark、秒級完成。
• DELETE：DML、產生 UNDO/REDO、可 Rollback、慢。
• 需保留 HWM 或觸發 Trigger 時才用 DELETE。

9. 頻繁 COMMIT 釋放資源

批次處理中適度提交，釋放：UNDO 空間、鎖、Redo Log Buffer、內部管理開銷。避免超大交易導致回滾段暴增、鎖升級。

10. 索引與統計資訊維護

• 定期 DBMS_STATS.GATHER_SCHEMA_STATS（建議 METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'）。
• 監控 V$SQL_PLAN 確認索引被使用，清理無用索引（參考 V$OBJECT_USAGE）。
• 外鍵欄位**必須建索引**，否則父表 DML 導致子表全表鎖。

進階策略清單

1. 分區表/分區索引：大表按時間/範圍分區，實現 Partition Pruning。
2. 物化視圖：預計算複雜彙總，查詢重寫自動導向。
3. 結果快取：RESULT_CACHE 子句/模式，熱點查詢零執行。
4. SQL Plan Baseline：固化優秀計畫，防止效能倒退。
5. 適應性執行計畫 (12c+)：運行時根據實際行數動態切換連接方法。
6. In-Memory Column Store (12.1.0.2+)：熱點表載入記憶體列式格式，分析查詢加速 10-100x。