相关子查询在oracle 21c中跑得慢，因其每次外层行都执行一次，易触发大量嵌套循环（nl），导致cpu和逻辑读飙升；优化器不会自动将其全部改写为join，尤其涉及聚合、rownum或外层列引用时需人工干预。
为什么相关子查询在Oracle 21c里跑得慢？
相关子查询（correlated subquery）每次外层行都要执行一次，数据量稍大就容易触发大量嵌套循环（NL），执行计划里常见 VIEW + NESTED LOOPS 组合，CPU和逻辑读飙升。Oracle 21c虽然优化器更智能，但不会自动把所有相关子查询改写成JOIN——尤其涉及聚合、ROWNUM、或子查询含WHERE引用外层列时，改写必须人工干预。
关键判断：只要子查询只返回单值（标量）、不依赖ORDER BY或分页逻辑，且外层与子查询表存在明确关联条件，就具备改写基础。
怎么把标量子查询（SELECT ... FROM dual WHERE ...）改成LEFT JOIN？
这类最常见，比如查每个员工的部门名称：
SELECT e.emp_id, e.name,
(SELECT d.dept_name FROM dept d WHERE d.dept_id = e.dept_id) dept_name
FROM emp e;
直接改写为LEFT JOIN，避免NULL被过滤：
- 用
LEFT JOIN替代子查询，确保emp主表所有行保留 - 连接条件必须和子查询
WHERE一致（e.dept_id = d.dept_id） - 子查询若可能无匹配（如部门被删），
LEFT JOIN后字段为NULL，行为一致；若用INNER JOIN会丢行 - 如果子查询有额外过滤（如
d.status = 'ACTIVE'），必须写进ON子句，不能放WHERE——否则变成交叉过滤，语义错乱
改写后：
SELECT e.emp_id, e.name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id AND d.status = 'ACTIVE';
含聚合的相关子查询（MAX/MIN/COUNT）怎么JOIN？
例如查每个部门最新入职员工的姓名：
SELECT d.dept_id, d.dept_name,
(SELECT e.name FROM emp e
WHERE e.dept_id = d.dept_id
AND e.hire_date = (SELECT MAX(e2.hire_date) FROM emp e2 WHERE e2.dept_id = d.dept_id))
FROM dept d;
这种不能简单JOIN，因为聚合结果需先算出“每部门最大入职日期”，再关联员工。正确路径是：先用GROUP BY子查询或CTE预计算聚合值，再JOIN回原表：
- 优先用
WITHCTE分离聚合逻辑，提高可读性和优化器识别度 - 聚合结果必须包含用于关联的键（如
dept_id）和聚合值（如max_hire_date） - 后续
JOIN要同时匹配部门ID和日期，避免多行重复 - Oracle 21c支持
LATERAL（类似PostgreSQL的JOIN LATERAL），但需确认版本补丁是否启用；默认不推荐，兼容性和执行计划不稳定
稳妥写法：
WITH dept_max AS ( SELECT dept_id, MAX(hire_date) max_hire_date FROM emp GROUP BY dept_id ) SELECT d.dept_id, d.dept_name, e.name FROM dept d LEFT JOIN dept_max dm ON d.dept_id = dm.dept_id LEFT JOIN emp e ON e.dept_id = dm.dept_id AND e.hire_date = dm.max_hire_date;
哪些情况根本没法安全JOIN？
不是所有相关子查询都能改。以下场景强行JOIN会导致结果错误或性能更差：
- 子查询含
ROWNUM 且未配合<code>ORDER BY：语义是“任取一行”，JOIN会返回所有匹配行 - 子查询引用外层多列且存在函数转换（如
UPPER(e.name) = UPPER(d.name)），无法直接下推为等值连接条件 - 子查询带
EXISTS或NOT EXISTS：应改写为LEFT JOIN ... WHERE xxx IS NULL，但要注意空值陷阱（NULL != NULL） - 子查询中使用
SYS_CONTEXT、USER等会话级函数：JOIN无法复现运行时上下文
遇到这些，先看执行计划里filter操作是否成为瓶颈；如果是，考虑物化中间结果（MATERIALIZED WITH CLAUSE）或加函数索引，而不是硬套JOIN。
