oracle去掉谓词推入,消除谓词推进

今天，还是用第一篇博文的例子，看下怎么消除谓词推进。首先先上图，这是一个发生了谓词推进的例子。

sql：

SELECT

COUNT(1) AS count

FROM (

SELECT a.ykf272, a.akb020, a.akb021,

a.yka094, a.ake005

, a.yke936, a.ykf282, a.aac003, a.aac001, a.aac002

, a.akc225, a.akc226, a.ykf275, a.ykf281,

a.ykf284

, a.ykf283, a.yke453, a.aae011, a.aae036,

a.yke923

, a.yke925, a.yab003, b.yka059, b.yka002,

b.yke627

, b.yka095, a.yke938, a.yke940, x.ake006

, pkg_common.fun_getAaa103('YKF062',

x.ykf062) AS ykf062

, (

SELECT c.aaa103

FROM bm_ykf063 c

WHERE b.ykf063 = c.ykf063

) AS ykf063, b.yka007, x.ykf064

, (

SELECT

pkg_common.fun_getAaa103('AKA070', c.aka070)

FROM ka02 c

WHERE b.yka002 = c.yka002

) AS aka070, pkg_common.fun_getAaa103('YKE469',

b.yke469) AS yke469, b.aka074

FROM kz37k1 a, ka05 b,(

SELECT

akb020, ake005, yka094, ake006, ykf062

,

ykf064

FROM

(

SELECT

x.akb020, x.ake005, x.yka094, x.ake006, x.ykf064

,

x.ykf062, DENSE_RANK() OVER (PARTITION BY x.akb020, x.ake005, x.yka094 ORDER BY

aae030 DESC) AS r

FROM

kb05k1 x

)

WHERE

r = 1

)

x

WHERE a.yka094 = b.yka094

AND a.akb020 = x.akb020

AND a.ake005 = x.ake005

AND a.yka094 = x.yka094

AND a.akb020 = '855855'

AND a.yab003 = '0001'

AND trunc(a.aae036, 'dd') >=

'08-APR-18'

AND trunc(a.aae036, 'dd') <=

'10-APR-18')a;

执行计划：

我们看到id=7这里发生了谓词推进。我们来看下谓语信息。

注意到id=7那里只是一个filter，所以谓词推进并不是发生在这一步，这里只是告诉你标黄的子查询部分发生了先被谓词推入然后作为一个整体参与后续的步骤。那真正发生谓词推入的地方在哪里呢？可以看到id=7到id=11最先执行的应当是id=11，而且这里循环了43782次，

也就是说，这一步索引范围扫描重复了43782次，我们往上看与第7步并列的是id=3，他们的父步骤id=2就是嵌套循环，而id=3最终得到的结果集(这一步的a-rows)就是43782行，这就不奇怪了，id=3的结果集作为驱动表，而id=7的结果集作为被驱动表，确实该发生这么多次循环，

但问题是，id=7的这个结果集并不是作为一个整体被遍历了43782次，而是从索引范围扫描就开始循环了，这是为什么呢？

我们看到sql中标黄部分的子查询，完全可以自成一体，只遍历一次，就可以得到想要结果集，为什么要遍历43782次,(这里其实是已经做过优化的，之前是max()函数，自联结的一个查询，为了不让这里被多次遍历，用分析函数优化了自联结)，这时候我们就该看看id=11的谓词信息是什么，

id=11显示走了index_..._AKE005的索引，谓词信息表明它具体走了索引的哪写列，发现本该在where条件中的x表和a表的连接条件出现在了这里，这就是导致这里发生了43782次循环的根源，这里发生的就是谓词推入，把本不该是子查询的条件推入到子查询中，明显这里不适合这样做。

现在我们发现了问题的原因，那么怎么能阻止谓词推入呢？我们去找找outline部分里面对应该行为的hint。

找到了，就是这里，所以很简单了，我们只需要把这句复制出来，把push_pred改成no_push_pred，然后再把它加入到第一个select后面的hint中。

现在我们再看执行计划：

我们看到id=4的starts列变为1，表明这个子查询现在是按照我们理想的只执行了一次。

小结一下：

谓词推入是cbo一种优化的行为，但是谓词推入并不总是好的，如果谓词被推入后缩小了结果集，降低了逻辑读，那就是好的，反之就不太好，其实cbo并不是只要有谓词推入的机会就会推入，由于统计信息不准确，倾斜的列上没有直方图，都会导致cbo在计算cost

的时候产生较大偏差，而对于大的业务表，数据变化频繁，也不能总收集统计信息，所以这时候我们就需要去找到合适的hint来协助cbo做出最优的选择。