Oracle 优化器“子查询取消嵌套（Subquery Unnesting）

在 OLTP 与 OLAP 场景里，子查询无处不在。开发者爱用子查询，因为它语义直观；优化器却“头痛”，因为嵌套结构限制了访问路径、连接方法和连接顺序的选择。 Oracle 优化器给出的解决方案之一便是 子查询取消嵌套（Subquery Unnesting）：把子查询重写成等价的 JOIN，让后续优化空间瞬间打开。

---

概念速览

术语	说明
子查询取消嵌套	将嵌套子查询提升为父查询的 JOIN 操作，使优化器能把子查询表与父查询表放在同一张“决策桌”上，统一考虑访问路径、连接方法与顺序。
触发条件	1. 语义等价（结果集完全一致）； 2. 子查询不含聚合函数（如 AVG, SUM, MAX…）； 3. 其他限制（CONNECT BY, ROWNUM, 集合运算等会阻止转换）。
失败回退	如果无法转换，优化器把父查询和子查询当成两条独立语句，先执行子查询，再把结果回传父查询。

---

实战剖析

3.1 原始 SQL：最常见的 IN 子查询

-- 以 SH schema 为例
SELECT *
FROM   sales
WHERE  cust_id IN (SELECT cust_id
                   FROM   customers);

3.2 为什么可以取消嵌套？

• customers.cust_id 是 主键，唯一且非空。
• 语义上，sales 表只要匹配到 customers 中任意一行即可，等价于两表按 cust_id 做 半连接（Semi Join）。
• 子查询无聚合，无 ROWNUM，无 CONNECT BY。 → 优化器判定：可以安全转换。

3.3 转换后的等价 JOIN（半连接写法）

-- 半连接（SEMI JOIN）形式，Oracle 内部执行计划常见
SELECT s.*
FROM   sales     s,
       customers c
WHERE  s.cust_id = c.cust_id;

注意：

1. 优化器实际生成的计划可能是 HASH JOIN SEMI 或 NESTED LOOPS SEMI，但逻辑上已等价。
2. 上述写法只是为了演示，不要手工改写；让优化器自动完成即可。

---

执行计划对比

场景	执行计划特征	性能差异
未取消嵌套	子查询先执行 → 生成结果集 → 父查询用 IN-LIST ITERATOR 或 FILTER 逐行判断	无法使用哈希/排序合并连接，易放大行数；子查询可能被反复执行。
取消嵌套后	父查询与子查询表一次性做 半连接	可使用哈希连接、并行、分区智能扫描等；内存、CPU 开销显著降低。

你可以用以下命令验证：

EXPLAIN PLAN FOR
SELECT *
FROM   sales
WHERE  cust_id IN (SELECT cust_id FROM customers);
​
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

若看到 HASH JOIN SEMI、NESTED LOOPS SEMI 或 MERGE JOIN SEMI，就代表优化器已做了子查询取消嵌套。

---

何时不能取消嵌套？

反例 SQL	原因
SELECT * FROM sales WHERE amt > (SELECT AVG(amt) FROM sales)	含聚合函数 AVG。
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept CONNECT BY PRIOR deptno = parent_deptno)	有 CONNECT BY。
SELECT * FROM t WHERE id IN (SELECT id FROM t2 WHERE ROWNUM < 10)	含 ROWNUM。

遇到这些情况，优化器会退回到 FILTER 或 子查询缓存 策略。

---

给开发者的 3 条实践建议

1. 主键/唯一键是你的朋友 确保连接列上有 主键、唯一索引或至少非空+索引，可显著提高取消嵌套成功率。
2. 别急着手工改写 手写 JOIN 并不总是更快；让优化器先尝试，用执行计划验证。
3. 必要时用 Hint 如果优化器误判，可强制转换： SELECT /*+ UNNEST */ *
   FROM   sales
   WHERE cust_id IN (SELECT cust_id FROM customers);反之，也可禁止： SELECT /*+ NO_UNNEST */ …

子查询取消嵌套是 Oracle 优化器里最常用、最有效的查询重写之一。理解其原理与边界，既能让我们写出更高效的 SQL，也能在调优时迅速定位“为什么这条 SQL 不走索引/不 JOIN”。 下次遇到慢 SQL，先看看执行计划，也许只是优化器缺了一把“主键”这把钥匙。

---