Oracle WITH 子句性能陷阱：謂詞無法下推導致全表掃描，改寫後從 2 小時降至 12 秒

Oracle WITH 子句性能陷阱：謂詞無法下推

開發為了邏輯清晰大量使用 WITH 子句（子查詢因子化），將中間結果集緩存。但優化器無法將外層謂詞下推到 WITH 定義的內部視圖，導致索引失效、全表掃描。實測優化前 2+ 小時，改寫後 12 秒。

場景重現與對比

建測試表與索引：

DROP TABLE test PURGE;
CREATE TABLE test AS SELECT * FROM dba_objects;
CREATE INDEX ind_t_object_id ON test(object_id) NOLOGGING;
EXEC dbms_stats.gather_table_stats(USER, 'test', cascade => TRUE);

SQL1：優化前（謂詞未下推）

WITH a AS (SELECT * FROM test WHERE object_type='TABLE'),
     b AS (SELECT COUNT(1) FROM a WHERE object_id < 10),
     c AS (SELECT COUNT(1) FROM a WHERE object_id >= 10 AND object_id < 20)
SELECT (SELECT * FROM b) bc, (SELECT * FROM c) cc FROM dual;

執行計劃關鍵點：

• 建立臨時表 SYS_TEMP_0FD9D66 存儲 a 的結果（1600 行）。
• 後續 b、c 對臨時表做 FULL TABLE SCAN 過濾 object_id，索引 ind_t_object_id 完全未使用。
• 統計信息：589 consistent gets。

SQL2：優化後（謂詞下推，索引生效）

WITH b AS (SELECT COUNT(1) FROM test WHERE object_id < 10),
     c AS (SELECT COUNT(1) FROM test WHERE object_id >= 10 AND object_id < 20)
SELECT (SELECT * FROM b) bc, (SELECT * FROM c) cc FROM dual;

執行計劃關鍵點：

• 直接對 test 表做 INDEX RANGE SCAN（object_id 範圍）。
• 無臨時表轉換開銷。
• 統計信息：4 consistent gets（性能提升 147 倍）。

核心結論

• WITH 子句會物化中間結果（TEMP TABLE TRANSFORMATION），阻斷謂詞下推。
• 若後續查詢需利用索引，勿將帶過濾條件的查詢放入 WITH 物化，應直接在使用處寫入過濾條件。
• 追求代碼可讀性與性能需平衡：可用 /*+ INLINE */ 提示嘗試內聯，或改用視圖/內聯視圖。