oracle hint push_pred,oracle三组难缠的hint 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也应该较小。

2.push_subq如果说no_unnest是为了让子查询不展开，独立的完成，那么push_subq就是为了让子查询最先进行join。

所以，这个hint其实是控制的join的顺序。

SQL> create table tmp_liuhc_3 as select * from dba_objects;

Table created

SQL> create table tmp_liuhc_4 as select * from dba_objects;

Table created

SQL> analyze table tmp_liuhc_3 compute statistics;

Table analyzed

SQL> analyze table tmp_liuhc_4 compute statistics;

Table analyzed

SQL> create index idx_tmp_liuhc_3 on tmp_liuhc_3(object_id);

Index created

3. push_pred

对应push_pred这个hint，首先要搞清楚mergeable view和unmergeable view的区别。

这个在concept上有明确解释：

Mergeable and Unmergeable ViewsThe optimizer can merge a view into a referencing query block when the view has one or more base tables, provided the view does not contain:

set operators (UNION, UNION ALL, INTERSECT, MINUS)

a CONNECT BY clause

a ROWNUM pseudocolumn

aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list

When a view contains one of the following structures, it can be merged into a referencing query block only if

complex view merging is enabled (as described below):

a GROUP BY clause

a DISTINCT operator in the select list

View merging is not possible for a view that has multiple base tables if it is on the right side of an outer join. If a view on the right side of an outer join has only one base table, however, the optimizer can use complex view merging even if an expression in the view can return a non-null value for a NULL. See

"Views in Outer Joins" for more information.

这里在最后，我们发现一个unmergeable view的一种情况就是view在outer join的右侧。

对于这种情况，我们熟知的merge hint也无效。

对于这样一个简单的查询，可见谓词TMP_LIUHC_3.object_name=tmp_liuhc_view.object_name被merge到了view中，那么我把tmp_liuhc_view放到outer join的右侧，这是tmp_liuhc_view就属于unmergeable view了，优化器默认无法将谓词merge进这个tmp_liuhc_view中，于是就看到了tmp_liuhc_view单独先执行：

那么我们使用hint push_pred强制优化器将谓词merge进view中，可见到“VIEW PUSHED PREDICATE”：

虽然merge hint会有同样的效果，但是对于这种unmergeable view来说，merge hint是无效的。

可见，对于此种身处outger join右侧的view来说，merge hint已经无能为力了。 综上，对于大家比较容易混淆的三个hint： no_unnest/unnest是针对子查询是否展开的，push_subq是针对子查询的连接顺序的，push_pred则是针对unmergeable view使用外部查询谓词。