撰寫高效能 SQL 的 22 條實戰準則

SQL 語句優化實戰指南

優化 SQL 不僅是調整索引，更是對業務邏輯、資料分佈與執行計畫的深度理解。以下 22 條建議涵蓋查詢重寫、索引策略、連接優化與進階特性應用，助您編寫出高效、穩定的 SQL 程式碼。

查詢邏輯與重寫技巧

1. 合理選擇全表掃描： 當索引選擇度低（回表行數超過表總行數 10%-15%）或需讀取大部分資料時，全表掃描反而比索引範圍掃描更高效。
2. 優化子查詢： 優先考慮將 IN/EXISTS 子查詢轉換為 JOIN，或使用 WITH 子句（CTE）物化中間結果，減少重複掃描。
3. 用 NOT EXISTS 替代 NOT IN： NOT IN 在含有 NULL 值時語義陷阱多，且難以利用索引；NOT EXISTS 語義明確，通常能走反連接（Anti-Join）優化。
4. LIKE 前綴匹配代替 SUBSTR： WHERE col LIKE 'ABC%' 可利用索引範圍掃描，而 SUBSTR(col,1,3)='ABC' 會導致函數索引失效（除非建立函數索引）。
5. 善用 NVL/COALESCE 處理空值： 在 WHERE 條件或連接鍵中適當使用 NVL(col, 'default') 避免隱性轉換，但需注意可能阻礙索引使用，建議改寫為 col IS NULL OR col = 'val'。
6. 複雜 OR 條件改寫為 UNION ALL： 多個 OR 條件涉及不同列索引時，拆分為多個 UNION ALL 子查詢可分別利用各自索引，再合併結果（注意去重邏輯）。

索引設計與應用策略

7. 建立合適的索引： 遵循「選擇度高、常用於 WHERE/JOIN/ORDER BY 的列優先」原則，避免過度索引導致 DML 開銷過大。
8. 外鍵列務必建索引： 主從表關聯查詢或主表刪除/更新時，子表外鍵無索引會觸發全表掃描鎖定，嚴重影響並發效能。
9. 活用複合索引： 將高選擇度列放在前導位置，滿足「最左前綴」原則，一個複合索引可服務多種查詢模式。
10. 非唯一索引支援唯一約束： 允許暫時延遲驗證（ENABLE NOVALIDATE）或載入歷史資料時，非唯一索引可避免唯一索引的嚴格檢查鎖衝突。
11. 低基數列考慮位圖索引： 適用於資料倉庫、批次處理環境中性別、狀態等低基數列，配合 AND/OR/NOT 邏輯運算可快速合併 Rowid 集合；嚴禁用於高並發 OLTP 寫入場景。
12. 單表雜湊/索引叢集： 針對固定連接鍵、高頻等值連接的單表場景，可減少 I/O 與連接運算開銷。

進階優化與架構層面

14. 審慎使用 View： 視圖嵌套過深會導致最佳化器無法有效推導述詞下推，建議直接查詢基表或使用物化視圖預計算。
15. 避免遠端存取： 分布式查詢（@dblink）網路延遲高、難以優化執行計畫，盡量在本地複制資料或使用同步機制。
16. 主導連接方法選擇： 透過 USE_NL、USE_MERGE、USE_HASH Hint 或調整驅動表大小，引導最佳化器選擇巢狀迴圈、排序合併或雜湊連接。
17. 啟用陣列處理與批次綁定： JDBC addBatch()、OCI OCIStmtExecute 陣列模式可大幅減少網路往返與解析開銷。
18. 動態 SQL 優先用 EXECUTE IMMEDIATE： 相比 DBMS_SQL 原生動態 SQL，EXECUTE IMMEDIATE 程式碼更簡潔、效能更優（內部最佳化）。
19. 超大表實施分區： 範圍、清單、雜湊分區可實現分區修剪、分區並行掃描、獨立維護，大幅降低維護窗口與查詢範圍。
20. RBO 模式下 FROM 子句順序技巧： 僅適用於舊版本 RBO，將小表放在 FROM 最後作為驅動表；現代 CBO 已不依賴此規則。
21. 調大 SORT_AREA_SIZE 加速建索引： Session 級設定大排序區讓索引建立排序在記憶體完成，減少臨時表空間 I/O（12c+ 建議用 PGA_AGGREGATE_TARGET 自動管理）。
22. 持續測試與基準比對： 建立自動化 SQL 回歸測試流程，每次變更後對比執行計畫、邏輯讀、執行時間，防止效能倒退。

總結： SQL 優化無銀彈，需結合業務場景、資料量級、並發特性綜合權衡。養成「先 EXPLAIN PLAN、再實測、後上線」的習慣，方能行穩致遠。