Oracle WITH 子查詢因子化實戰：可讀性與性能的雙重提升

Oracle WITH 子查詢因子化實戰：可讀性與性能的雙重提升

Oracle WITH 子句（Subquery Factoring）將複雜查詢拆解為命名的臨時結果集，提升可讀性，並允許優化器物化複用中間結果。本文從語法基礎、多層嵌套、物化機制、性能適用場景、避坑指南五方面全景解析。

基礎語法

WITH RECURSIVE org_path AS (
  SELECT id, name, pid, CAST(name AS VARCHAR2(4000)) AS path
  FROM org WHERE pid IS NULL
  UNION ALL
  SELECT o.id, o.name, o.pid, p.path || '>' || o.name
  FROM org o JOIN org_path p ON o.pid = p.id
)
SELECT * FROM org_path ORDER BY path;

避坑指南

• CTE 僅在當前語句可見，不可跨 Session/Transaction 複用。
• 過度物化會增加臨時表空間壓力，觀察 V$TEMPSEG_USAGE。
• 舊版本（10g 前）不支援 WITH，需改用內聯視圖或全局臨時表。
• WITH 內不可包含 DDL/DML，僅限查詢。