love wife & love life —Roger的Oracle&MySQL技术博客

Phone:18180207355 提供专业Oracle&MySQL数据恢复、性能优化、迁移升级、紧急救援等服务

about subquery unnest/push pred

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger的Oracle&MySQL技术博客

本文链接地址: about subquery unnest/push pred

落落的sql优化班级确实牛叉,我也试听了1次,学不到不少东西,如果大家有兴趣,可以加他的试听QQ群:179221471

今天抽空做了下关于子查询和谓词推入的实验,我这里通过vm 10gR2环境来模拟展示。

上面的例子中,oracle自动将子查询进行了展开,下面我们使用no_unnest hint来不让oracle在这里进行子查询展开,就通过
嵌套在子查询里面,那么这样比如就会走fiter了,如下:

 

在10g中,oracle默认就会进行子查询的展开,这是通过一个隐含参数来进行控制的,如下:
SQL> show parameter unnest

NAME                                 TYPE        VALUE
———————————— ———– ——————————
_distinct_view_unnesting             boolean     FALSE
_unnest_subquery                     boolean     TRUE
SQL>

我们可以发现,_unnest_subquery 参数默认是true.

当子查询返回的结果集如果很小的话,这个时候其实是可以走fiter的,换句话讲,这个时候就可以不进行子查询的展开。

那么什么情况下存在子查询的情况,oracle不会进行子查询的展开呢 ?

—包含rownum

 

—包含cube、rollup 等函数

—包含union all、union、INTERSECT、 MINUS等

—包含start with…connect by

 

我们再来看下试图合并,首先我们创建几个测试表。

从上面可以看到t3的条件进行了fiter操作,并没有推进到view里面去,下面我们使用push_pred hint进行强制推进下。

当sql查询中出现view,实际上使用merge/no_merge和push_pred,no_push_pred我感觉有些类似。

如下一段关于merge 和 push_pred的描述:

Merge / no_merge: if you use a complex view (e.g. aggregate view, or join view) in your query, should you rewrite
the query to merge the tables in the view into a single from clause with all the other tables (merge), or should
you evaluate the view to produce a “standalone” result set and then join the result set to the remaining tables (no_merge).

Push_pred / no_push_pred: If you have a non-mergeable view (possible because of a no_merge hint) in your query, how
should you operate the join from other tables; should you create one large view result and join it once (no_push_pred)
or should you push the join predicate down into the view definition and recreate the view result set for every driving
row from another table (push_pred).

merge 就是把view展开,那么你查看执行计划时就看不到view acces patch的信息了,no_merge则相反。
所以push_pred(谓词推入)就是是说将谓词条件推入到view中。

由于要发现push pred操作,必须保证试图不能被merge,所以通常在实验观察时会同时使用no_merge hint.

细心一点我们还能发现,针对谓词推入的操作,其外部操作只能是nest loop。

到最后我们可以简单的总结下,针对如下情况,子查询是不能展开的:

1.子查询存在 ROWNUM
2.子查询存在 CUBE,ROLLUP
3.子查询存在 UNION, UNION ALL,INTERSECT、 MINUS
4.子查询存在 START WITH ,CONNECT BY 字句

通常我发现针对右外连接的情况下,oracle优化器默认也不会进行view merge操作,如下:

事实上针对这样的情况,强制使用merge hint也不起作用,如果不存在右外连接的情况下,是可以自动进行merge的:

该默认行为是oracle通过一个参数来进行控制的,如下:
SQL> show parameter view

NAME                                 TYPE        VALUE
———————————— ———– ——————————
_complex_view_merging                boolean     TRUE
_distinct_view_unnesting             boolean     FALSE
_partition_view_enabled              boolean     TRUE
_project_view_columns                boolean     TRUE
_push_join_union_view                boolean     TRUE
_push_join_union_view2               boolean     TRUE
_simple_view_merging                 boolean     TRUE
optimizer_secure_view_merging        boolean     TRUE

在10gR2版本中 _simple_view_merging 参数默认是true。

One Response to “about subquery unnest/push pred”

  1. 坝上草原自驾游www.bashanglvyou.com Says:

    不错 写的很好

Leave a Reply

You must be logged in to post a comment.