UNNEST提示必须加在子查询的SELECT后而非外层,否则无效;它仅作用于子查询块本身,需配合QB_NAME防重命名,并要求子查询结构可展开(如不含ROWNUM、集合操作或分析函数)。

UNNEST提示必须加在子查询的SELECT后,不是外层
很多人把/*+ UNNEST */写在外层SELECT后面,结果完全没效果。Oracle只认子查询块内部的UNNEST提示——它作用对象是子查询本身,不是整个语句。
常见错误现象:EXPLAIN PLAN里依然看到FILTER操作,子查询被反复执行(N次),逻辑读暴增;DBMS_XPLAN.DISPLAY输出中查不到UNNEST相关字样。
- 正确位置:子查询的
SELECT关键字紧后,例如SELECT /*+ UNNEST */ ... FROM ... - 不能写成
SELECT /*+ UNNEST */ * FROM t1 WHERE x IN (SELECT /*+ UNNEST */ y FROM t2)——这里外层的UNNEST无效,只有括号里的那个才起作用 - 如果子查询带相关条件(比如
e.dept_id = d.dept_id),需配合QB_NAME确保块名稳定,避免优化器重命名后提示失效
UNNEST生效的前提:子查询结构得“可展开”
不是所有子查询都能被UNNEST。Oracle会直接拒绝某些结构,哪怕你写了提示,执行计划里也看不到任何变化。
典型不可展开场景:
- 子查询含
ROWNUM(如WHERE ROWNUM = 1) - 子查询含聚合但无
GROUP BY且非标量子查询(比如(SELECT MAX(x) FROM t)本身可展开,但(SELECT COUNT(*) FROM t GROUP BY y)不行) - 子查询含集合操作(
UNION、INTERSECT等),11.2之前不支持展开 - 子查询含分析函数(
OVER子句)
验证方法:先跑无Hint版本,用SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)看是否已有NESTED LOOPS SEMI或HASH JOIN SEMI——如果有,说明已自动展开,UNNEST提示冗余;如果只有FILTER,再加提示并对比。
UNNEST和NO_UNNEST容易混淆的两个陷阱
这两个提示名字像反义词,但实际行为和适用对象常被搞反。
-
NO_UNNEST≠ “让子查询嵌套”,而是“禁止优化器对这个子查询做展开”——它保留FILTER路径,子查询作为最后一步执行 -
UNNEST≠ “强制合并视图”,那是MERGE/NO_MERGE的事。把NO_UNNEST错用在FROM子句的内联视图上(比如SELECT * FROM a JOIN (SELECT * FROM b) v ON ...),根本不会生效——这种结构属于视图,该用NO_MERGE(v) - 当子查询被展开后,原表别名可能被重写(比如子查询里
t1变成T1@SEL$2),所以后续索引提示必须用@SEL$2定位,否则INDEX(t1 idx_x)会挂到外层表上
为什么有时UNNEST没效果?先检查查询转换开关
有些环境(尤其是老版本或打了特定Patch的11.2.0.4),UNNEST可能被隐式禁用。最直接的排查方式是查参数:
- 运行
SELECT name, value FROM v$parameter WHERE name IN ('_unnest_subquery', '_optimizer_unnest_all'),确认值不是FALSE - 临时启用:
ALTER SESSION SET "_optimizer_unnest_all" = TRUE(仅测试用,勿上线) - 更稳妥的替代:如果
UNNEST始终不生效,改用改写方式——把子查询提前为WITH子句并加MATERIALIZE,再在外层JOIN,往往比死磕提示更可控
真正难的不是写对UNNEST,而是判断它该不该展开:当子查询结果集极小(比如固定返回1行),NO_UNNEST反而更快;而UNNEST真正发挥价值的,是子查询选择性高、能走高效连接时——这点容易被忽略,光盯着语法,忘了业务数据分布。


















