Oracle WITH 子查詢 (CTE) 語法與效能優化實戰

前言

WITH 子句 (Common Table Expression, CTE) 讓複雜查詢模組化、可讀性大增。Oracle 11g+ 支援 **Materialize Hint** 將 CTE 具體化為臨時表，避免重複解析與運算，提升效能。本文涵蓋語法、範例、具體化機制與避坑指南。

標準語法

WITH 
  alias_name1 AS (subquery1),
  alias_name2 AS (subquery2),
  ...
  alias_nameN AS (subqueryN)
SELECT col1, col2, ...
FROM   alias_name1, alias_name2, ..., alias_nameN
WHERE  ...;

基本範例：多次引用同一子查詢

WITH 
  q1 AS (SELECT 3 + 5 AS s FROM DUAL),
  q2 AS (SELECT 3 * 5 AS m FROM DUAL),
  q3 AS (SELECT s, m, s + m AS sum_sm, s * m AS prod_sm FROM q1, q2)
SELECT * FROM q3;
-- 結果: S=8, M=15, SUM_SM=23, PROD_SM=120

進階：具體化 (Materialize) 與 Inline 展開

強制具體化範例

WITH 
  sales_agg AS (
    SELECT /*+ MATERIALIZE */ 
           product_id, SUM(amount) AS total_amt
    FROM   sales
    WHERE  sale_date >= TRUNC(SYSDATE) - 30
    GROUP BY product_id
  )
SELECT s.product_id, p.name, s.total_amt
FROM   sales_agg s
JOIN   products p ON p.id = s.product_id
WHERE  s.total_amt > 10000
UNION ALL
SELECT s.product_id, p.name, s.total_amt
FROM   sales_agg s
JOIN   products p ON p.id = s.product_id
WHERE  s.total_amt BETWEEN 5000 AND 10000;

sales_agg 只掃描/彙總一次，兩個 UNION ALL 分支直接讀取臨時表。

遞迴 CTE (Hierarchical Query) — 11gR2+

WITH org_tree (emp_id, mgr_id, lvl, path) AS (
  SELECT emp_id, mgr_id, 1, TO_CHAR(emp_id)
  FROM   employees
  WHERE  mgr_id IS NULL               -- Anchor: 根節點
  UNION ALL
  SELECT e.emp_id, e.mgr_id, ot.lvl + 1, ot.path || '>' || e.emp_id
  FROM   employees e
  JOIN   org_tree ot ON e.mgr_id = ot.emp_id  -- Recursive: 子節點
  WHERE  ot.lvl < 10                  -- 防無限遞迴
)
SELECT * FROM org_tree ORDER BY path;

最佳實務與避坑

1. 命名清晰：CTE 別名使用動詞名詞 (如 filtered_orders, monthly_sales) 提升可讀性。
2. 僅在必要時具體化：小結果集、單次引用用 Inline；大結果集、多次引用、非確定性函數 (如 SYSDATE, DBMS_RANDOM) 用 MATERIALIZE。
3. 統計資訊缺失風險：具體化臨時表無統計資訊，優化器假設 100 行 → 可能選錯連接方式。可用 /*+ OPT_ESTIMATE(TABLE sales_agg ROWS=50000) */ 輔助估算。
4. 遞迴深度限制：務必加 WHERE lvl < N 或 CYCLE emp_id SET is_cycle TO 1 DEFAULT 0 (12c+) 防循環。
5. 不可在 CTE 內使用 DML**：Oracle CTE 僅支援 SELECT，需 DML 請用 MERGE 或 PL/SQL。