oracle三组难缠的hint no_unnest/unnest,push_subq,push_pred

Oracle提示：no_unnest, unnest与push_subq、push_pred解析

最新推荐文章于 2025-01-09 14:30:44 发布

原创

最新推荐文章于 2025-01-09 14:30:44 发布 · 6.1k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #merge #join #table #listview #filter

本文探讨了Oracle SQL优化中涉及的三个复杂提示：no_unnest、unnest和push_subq、push_pred。no_unnest防止子查询展开，保持其嵌套状态，而unnest则相反。push_subq影响子查询的连接顺序，push_pred用于不可合并视图的外部查询谓词。通过示例解释了这些提示如何影响查询执行计划和优化。

最近在看一个哥们优化sql里面,使用到了几个特殊的hint，但我总是对此不解，所以针对此问题自己做了一些测试(参考了一些资料)

1. no_unnest, unnest
unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套(nest)在里面。
所以un_unnest双重否定代表肯定，即让子查询不展开，让它嵌套(nest)在里面。

如果一个查询中的where 中出现 view 或者子查询。那么oracle的CBO在解析着这个SQL之前要做转换，把VIEW或者子查询"打开"~ 这个操作就叫做unnest~ 然后可以把主查询和子查询中的表通过表连接的方式，生成执行计划。你可以使用NO_UNNEST这个hint来告诉CBO，不要“打开” VIEW或者子查询~ 这样VIEW或者子查询只能被当作一个独立查询，来进行解析，里面的表无法和主查询中的表进行连接

现做一个简单的实验：

SQL> select count(*) from dba_objects;
COUNT(*)
----------
60494

这里子查询自动展开（unnest），即TMP_LIUHC_1和TMP_LIUHC_2 hash join在一起。
接下来如果我们不希望TMP_LIUHC_2展开，想先让它单独的执行完，然后再来和外部查询进行一种叫做FILTER的操作。
那么我们加入hint no_unnest:

所以，什么时候该用no_unnest使得子查询能够独立的执行完毕之后再跟外围的查询做FILTER？
首先，子查询的返回结果集应该较小，然后外围查询的输入的distinct value也应该较小。