with子句仅在子查询被多次引用且计算开销高时才可能提升性能；盲目使用反因物化增加i/o开销，其效果取决于执行计划是否出现temp table transformation及统计信息准确性。
Oracle 的 WITH 子句本身不自动提速，它只在「子查询被多次引用」且「底层数据集较大、计算开销高」时才可能带来性能收益；盲目套用反而可能因物化（materialization）引入额外临时表空间 I/O 开销。
WITH 子句真正起效的两个前提
很多人以为只要加了 WITH 就能优化，其实不然。它的性能价值只在满足以下条件时兑现：
-
WITH定义的子查询在主查询中被引用 ≥ 2 次（比如 JOIN 两次、或在WHERE和SELECT中各用一次） - 该子查询本身含聚合（
GROUP BY）、连接（JOIN）、递归（CONNECT BY）或复杂过滤（如多层嵌套EXISTS），执行成本高 - 数据库未启用隐式重写（如 Oracle 12c+ 的
/*+ INLINE */提示可能绕过WITH物化）
WITH vs. 内联子查询：执行计划差异很关键
是否物化（即把结果真写入临时表空间）由优化器决定，不是语法强制。你必须看 EXPLAIN PLAN 中的 TEMP TABLE TRANSFORMATION 操作符是否存在：
- 有该操作符 → Oracle 实际物化了
WITH结果，后续引用走临时段，避免重复扫描 - 无该操作符 → 优化器选择内联展开（inline expansion），和没写
WITH效果一致，甚至更差（因解析开销略增） - 可通过
/*+ MATERIALIZE */提示强制物化，但需确认临时表空间充足，否则可能报ORA-1652
WITH 函数声明（12c+）容易忽略的执行陷阱
Oracle 12c 起支持在 WITH 中定义 PL/SQL 函数，但函数内调用 SQL 时行为易误判：
- 函数体内每执行一次
SELECT ... FROM dual或查表，都算一次独立执行 —— 不会因外层WITH物化而缓存结果 - 若函数被用于
SELECT列中且返回多行（如用PIPELINED），可能触发 N+1 查询问题 - 函数名优先级高于同名对象（如用户表
dept），若WITH dept AS (...)后又SELECT * FROM dept，实际查的是 CTE，不是原表
WITH 在递归查询中性能不可替代
树形结构（菜单、组织架构、BOM）的层次遍历，WITH 递归是唯一可读且可控的方式，且性能通常优于传统 CONNECT BY：
- 递归 CTE 明确分离锚点（anchor）和迭代（recursive）部分，逻辑清晰，便于加过滤条件限制层级深度
-
CONNECT BY的LEVEL和ORDER SIBLINGS BY无法在中间步骤做聚合，而递归 CTE 可在每次迭代中GROUP BY当前层级 - 当需要「自底向上」聚合（如子节点销量汇总到父节点），递归 CTE 的反向迭代比
CONNECT BY更自然，避免多次全树扫描
最常被忽略的一点：WITH 的性能收益高度依赖统计信息准确性和绑定变量窥探（bind peeking）是否生效。如果子查询里用了 :v1 这类绑定变量，而首次硬解析时传入的值导致优化器选错执行路径，后续所有复用都沿用错误计划 —— 此时 WITH 不是加速器，而是放大器。
