WITH子句本身不自动物化，仅语义重写为内联视图；仅当CTE被多次引用、优化器判定物化成本更低且满足版本与参数条件时才可能物化，否则仍重复执行。
WITH 子句本身不内联物化，所谓“内联物化”是误称；Oracle 中只有显式提示 /*+ MATERIALIZE */ 才能强制物化，且必须写在 CTE 定义的 AS 后紧邻位置，否则无效。
为什么 WITH 不等于自动物化
Oracle 的 WITH 子句默认只是语义重写（inline view 展开），不是缓存中间结果。即使你写了多层嵌套的子查询改用 WITH 拆分，只要没触发物化条件，优化器仍可能把每个 CTE 展开后重复执行——尤其当 CTE 仅被引用一次、或数据库版本 _with_subquery 未设为 MATERIALIZED 时。
常见错误现象：
- 执行计划里看不到
MATERIALIZE操作符，却以为“用了 WITH 就快了” - CTE 被多次引用，但因统计信息不准或成本估算偏高，优化器仍选择不物化
- 在 Oracle 10g 或更早版本中使用
/*+ MATERIALIZE */，提示被完全忽略
如何真正强制物化一个 CTE
唯一可靠方式是加 /*+ MATERIALIZE */ 提示，并严格遵守书写位置：必须紧跟在 CTE 名后的 AS 关键字之后、左括号之前。
正确写法示例：
WITH shipped_orders AS ( /*+ MATERIALIZE */ SELECT order_id, user_id FROM orders WHERE status = 'shipped' ) SELECT u.name, COUNT(*) FROM users u JOIN shipped_orders so ON u.id = so.user_id GROUP BY u.name;
容易踩的坑：
- 写成
SELECT /*+ MATERIALIZE */ ... FROM ...→ 提示作用于内层查询，不是 CTE 物化 - 拼错为
/*+ MATERIALISE */（英式拼写）→ Oracle 不识别 - CTE 名后漏空格，写成
AS/*+ MATERIALIZE */(→ 语法错误 - 在
WITH RECURSIVE的递归部分加该提示 → 无效，递归 CTE 不支持强制物化
深层嵌套场景下物化的实际价值与限制
当原始 SQL 是多层嵌套子查询（比如 3 层以上 SELECT ... FROM (SELECT ... FROM (SELECT ...))），改写为 WITH 并配合 /*+ MATERIALIZE */ 可带来两点实质收益：
- 避免同一逻辑块被重复计算（例如某聚合结果在主查询和
WHERE子句中各用一次） - 让执行计划更可控，便于用
EXPLAIN PLAN观察物化是否生效
但要注意：
- 物化会消耗临时表空间，若中间结果过大，可能引发
ORA-01652错误 - 物化不解决数据倾斜问题，反而可能放大内存压力
- 不能嵌套
WITH：所谓“深层嵌套 CTE”，只能是多个同级 CTE 用逗号分隔，不能在一个 CTE 定义里再写WITH
替代方案：什么时候不该强上 MATERIALIZE
如果 CTE 结果集很小（WHERE id IN (...)），强制物化反而增加 I/O 开销。此时更优做法是：
- 检查是否可将 CTE 内联展开 + 推谓词（predicate pushdown）
- 确认关联字段是否有索引，避免物化后 JOIN 仍走全表扫描
- 用
EXPLAIN PLAN对比加/不加提示的实际Cost和Bytes差异，而非凭经验判断
最常被忽略的一点：物化效果高度依赖统计信息准确性。如果 orders 表的行数、status 列直方图过期，优化器根本算不准物化是否划算——先 DBMS_STATS.GATHER_TABLE_STATS，再谈 MATERIALIZE。
