SQL 調優是 Oracle 效能優化投資報酬率最高的領域。以下彙整實戰驗證的核心技巧,涵蓋存取路徑、連接順序、函數使用、大量資料處理等面向。
Oracle 解析器以「由右至左」處理 FROM 子句,最後列出的表為驅動表。多表連接時,應將筆數最少的表放在最後(作為驅動表);三表以上連接,優先選擇交叉表(被其他表引用最多的表)作為驅動表。
解析器以「自下而上」解析 WHERE 子句。原則:表連接條件寫在前面,過濾性最強(回傳行數最少)的條件寫在最後,讓優化器盡早縮小結果集。
* 需透過資料字典展開所有欄位,增加解析負擔與網路傳輸量。明確列出所需欄位,亦利於計畫穩定。
每次 SQL 執行皆伴隨解析、綁定、執行、擷取等開銷。批次作業宜使用陣列繫結、FORALL、BULK COLLECT 等批量介面。
在 SQL*Plus、SQL*Forms、Pro*C 中將 ARRAYSIZE 設為 200(預設 15),單次擷取行數增大,網路往返大幅減少。
單次掃描搭配 DECODE 或 CASE 完成多條件分支聚合,替代多次掃描同一張表或自連接。
多個無關聯的簡單查詢可合併為單一 SQL(如 UNION ALL 或條件聚合),減少解析與往返。
DELETE FROM emp e
WHERE e.rowid > (SELECT MIN(x.rowid)
FROM emp x
WHERE x.emp_no = e.emp_no);
利用 ROWID 定位唯一保留行,避免全表掃描排序。
TRUNCATE 為 DDL,不產生 UNDO、不觸發 Trigger、瞬時釋放 High Water Mark,速度比 DELETE 快數個數量級。注意:不可回滾,不適用於部分刪除。
-- 12c+ 標準語法
SELECT * FROM t_order
ORDER BY order_date DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- 11g 相容寫法
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM t_order ORDER BY order_date DESC
) a WHERE ROWNUM <= 30
) WHERE rn > 20;
DBMS_STATS.GATHER_TABLE_STATS,啟用 AUTO_SAMPLE_SIZE 並收集 Histogram。SQL Plan Baseline 或 OPT_ESTIMATE Hint 固定優質計畫。掌握上述技巧並結合 EXPLAIN PLAN、SQL_TRACE、AWR/ASH 實測驗證,即能系統性解決 80% 以上的 SQL 效能問題。