Oracle 9i WITH 查詢語法深度解析與效能優化

WITH 子查詢因子化：簡化複雜 SQL 的利器

Oracle 9i 引入 WITH 子句 (Subquery Factoring)，可將子查詢命名並前置，大幅提升可讀性與效能。

基本語法範例

WITH 
  SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),
  OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)
SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K
FROM OBJ O, SEG S
WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+);

核心特性

• 可見性： 定義層中，後定義子查詢可引用前面已定義者
• 巢狀引用： 任意層級均可見
• 效能優化： 多次引用時自動建立暫存表，避免重複執行，減少 I/O

巢狀引用範例

WITH 
  Q1 AS (SELECT 3 + 5 S FROM DUAL),
  Q2 AS (SELECT 3 * 5 M FROM DUAL),
  Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;
-- 結果: S=8, M=15, S+M=23, S*M=120

效能實測對比

測試環境：10 萬筆測試表 T_WITH，查詢 ID 最小、最大、平均值對應記錄。

傳統寫法 (多次掃描)

SELECT ID, NAME FROM T_WITH
WHERE ID IN (
  SELECT MAX(ID) FROM T_WITH
  UNION ALL
  SELECT MIN(ID) FROM T_WITH
  UNION ALL
  SELECT TRUNC(AVG(ID)) FROM T_WITH
);

WITH 寫法 (單次掃描暫存)

WITH STATS AS (
  SELECT MAX(ID) MAX_ID, MIN(ID) MIN_ID, TRUNC(AVG(ID)) AVG_ID FROM T_WITH
)
SELECT ID, NAME FROM T_WITH, STATS
WHERE ID IN (STATS.MAX_ID, STATS.MIN_ID, STATS.AVG_ID);

WITH 版本僅掃描一次表，統計資訊暫存後多次引用，顯著降低 Consistent Gets 與 Physical Reads。

適用場景

• 複雜報表查詢：多重聚合、外部聯結邏輯清晰化
• 重複子查詢：同一邏輯多處引用
• 遞迴查詢前置處理：簡化主查詢結構