善用 WITH 子查詢大幅提升 Oracle 查詢效能

在 Oracle SQL 調優中，WITH 子句（Common Table Expressions, CTE）是簡化複雜查詢、避免重複掃描大表的利器。本文透過實戰案例，示範如何將繁瑣的多層子查詢重構為高效的 CTE 寫法，顯著降低資源消耗。

核心原理

WITH 子查詢會將中間結果集具體化至臨時段（記憶體或暫存表空間），後續主查詢可多次引用而無需重複存取基表。這對於「先聚合過濾，再多維度交叉分析」的報表型查詢極為有效。

實戰案例：稅務統計報表

情境：從明細表 ZSXX 抽取 2007/02~06 入庫資料，分別統計稅款(zt='01')、罰款(zt='02')、滯納金(zt='03')，最後按稅種合併呈現。

傳統寫法痛點

若直接在主查詢中重複引用 ZSXX 並套用不同 WHERE 條件，資料庫需多次全表掃描與分組，I/O 與 CPU 成本倍增。

WITH 子句優化寫法

WITH rkxx AS (
    -- 步驟 1：一次性預處理，過濾日期、聚合至稅種+狀態層級
    SELECT sz, zt, SUM(se) AS se
    FROM zsxx
    WHERE rkrq BETWEEN '2007-02' AND '2007-06'
    GROUP BY sz, zt
)
-- 步驟 2：主查詢多次引用輕量級 rkxx，進行條件分組與聯結
SELECT sk.sz,
       sk.se AS tax_amt,
       fk.se AS penalty_amt,
       znj.se AS overdue_amt
FROM (SELECT sz, SUM(se) se FROM rkxx WHERE zt = '01' GROUP BY sz) sk  -- 稅款
JOIN (SELECT sz, SUM(se) se FROM rkxx WHERE zt = '02' GROUP BY sz) fk  -- 罰款
  ON sk.sz = fk.sz
JOIN (SELECT sz, SUM(se) se FROM rkxx WHERE zt = '03' GROUP BY sz) znj -- 滯納金
  ON sk.sz = znj.sz;

關鍵效能優勢

• 減少基表存取：大表 ZSXX 僅掃描一次，後續運算在小得多的中間結果集上完成。
• 啟用並行與向量化：Oracle 可對 CTE 內部操作套用並行執行、Bloom Filter 等優化。
• 邏輯清晰可維護：業務邏輯模組化，便於除錯與擴充。

最佳實踐原則

1. 盡早過濾：在 CTE 定義階段就套用所有可下推的謂詞（日期、狀態等），壓縮中間結果體積。
2. 評估具體化成本：若 CTE 僅被引用一次且未減少資料量，優化器可能選擇內聯而非具體化，可用 /*+ MATERIALIZE */ 強制具體化。
3. 避免濫用：簡單查詢或單次引用場景，傳統子查詢或內聯視圖可能更輕量。
4. 統計資訊同步：確保基表統計資訊最新，優化器才能準確評估 CTE 基數與成本。

適用場景速查