with子句不能直接替代相关子查询，因其无法引用外部表字段；真正优化需解除相关性（如预计算部门平均值后join），或用提示改写执行计划。
WITH子句不能直接“替代”子查询逻辑，它只是把子查询提前命名并可能物化——是否提速、是否安全，全看你怎么用、在哪用、引用几次。
WHERE里嵌套子查询，别急着塞进WITH
比如查“工资高于本部门平均值的员工”：SELECT * FROM emp e WHERE sal > (SELECT AVG(sal) FROM emp WHERE dept_id = e.dept_id)。这种相关子查询每行都执行一次，强行挪进WITH不仅无效，还会报错（ORA-00904：e.dept_id 无法在WITH中引用）。
- 真正该优化的是执行计划：加
/*+ UNNEST */提示让优化器转成JOIN，或确保dept_id有索引 - 若想用WITH，得先解除相关性——例如预计算每个部门的平均值：
dept_avg AS (SELECT dept_id, AVG(sal) avg_sal FROM emp GROUP BY dept_id)，再JOIN回来 - 注意：如果
dept_avg只被引用一次，Oracle 21c 可能自动内联，WITH反而多一层解析开销
FROM里多次引用同一聚合结果，WITH才真有用
典型场景是报表中既要按区域汇总，又要算占比、又要排序：SELECT region, total_amt, total_amt / (SELECT SUM(total_amt) FROM sales_summary) pct FROM sales_summary。这里sales_summary被用了三次，但原写法会触发三次全量聚合。
- 正确写法：
WITH sales_summary AS (SELECT region, SUM(amount) total_amt FROM sales GROUP BY region) SELECT region, total_amt, total_amt / (SELECT SUM(total_amt) FROM sales_summary) pct FROM sales_summary - 关键点：必须确保
sales_summary在主查询中被引用≥2次，否则优化器大概率跳过物化 - 验证方式：查
V$SQL_PLAN，看到TEMP TABLE TRANSFORMATION和LOAD AS SELECT才算真正物化了
递归树形查询，WITH + CONNECT BY 是唯一可靠组合
Oracle 11g–19c 不支持标准WITH RECURSIVE，所谓“用WITH做递归”本质是WITH封装表、CONNECT BY负责递归。比如查某节点完整路径：SYS_CONNECT_BY_PATH必须出现在主查询，不能放在WITH内部。
- 错误写法：
WITH tree AS (SELECT ..., SYS_CONNECT_BY_PATH(...) path FROM t CONNECT BY ...)→ 报错ORA-32033或ORA-00942 - 正确结构：
WITH tree AS (SELECT cid, cname, parent_id FROM z_org WHERE org_level >= 1) SELECT SYS_CONNECT_BY_PATH(cname,'/') FROM tree START WITH parent_id = 0 CONNECT BY PRIOR cid = parent_id WHERE cid = 5 - 容易漏的坑：
START WITH必须落在根节点条件上（如parent_id IS NULL），过滤目标节点必须用主查询WHERE，写在CONNECT BY里会截断路径
强制物化或禁止物化，得靠提示控制
Oracle 21c 默认可能自动物化CTE，但不可控；手动加/*+ MATERIALIZE */又可能因写磁盘变慢——尤其当结果集小、基表有高效索引时。
- 要强制物化（确定需复用且结果集大）：
WITH sales_summary AS (SELECT /*+ MATERIALIZE NO_MERGE */ region, SUM(amount) s FROM sales GROUP BY region) - 要禁止物化（避免串行化、保留并行能力）：
WITH sales_summary AS (SELECT /*+ INLINE */ region, SUM(amount) s FROM sales GROUP BY region) - 物化失败常见于含
JSON_VALUE或向量函数的子查询，报错ORA-30926，此时只能改用INLINE或临时表
最常被忽略的一点：WITH定义的子查询列必须显式别名，且所有引用处都要用别名访问——不加别名或拼错，轻则ORA-00904，重则查出空结果却无报错。
