NO_PUSH_PRED提示的作用和使用方法
NO_PUSH_PRED 提示是指导优化器不要将连接谓词推入到视图或出现在from子句中的子查询中。
PUSH_PRED 提示的使用语法如下图所示:
其各部分的含义如下:
tablespec表示目标表的名称或别名(当有别名时,必须用别名)。但不要加入表的属主(SCHEMA)名,哪怕在SQL中,明确写了属主,提示中也不能写。
queryblock表示查询块。优化器在为一条SQL制定执行计划时,会将该SQL中涉及的子查询和视图,拆分为相应的查询块。分别为每一个查询块制定执计划。
下面,我们通过实验来说明施加该提示时,优化器是如何处理的。
测试验证
首先,我们创建测试表TESTTAB,并收集统计信息:
再创建另一个测试表TESTOBJ,并收集统计信息:
同时,在TESTOBJ表的object_name列上创建一个索引,如下图所示:
设置SQLPLUS下的环境参数,以便显示的内容方便查看。如下图所示:
发出以下查询:
select a.table_name,b.cnt
from testtab a
left join (select object_name,count(*) cnt from testobj group by object_name) b
on a.table_name=b.object_name
where a.owner='DEMO';
此时,Oracle优化器给出的执行计划如下所示:
从上图中的红框可知,优化器使用了谓词推入。从而可以避免对TESTOBJ表做一个全表扫描,生成中间结果后,再与TESTTAB表过滤后的中间结果做连接的处理方式。尤其是当TESTOBJ表较大时,这样做的开销会比较大。
但是,当统计信息陈旧,或者TESTOBJ表上的OBJECT_NAME列上的选择性不佳,或者驱动表估算返回的行数偏少时,这种处理方式,也许会更差。这时,我们就可以用NO_PUSH_PRED的提示,来指导优化器不使用谓词推入的处理方式。其示例SQL代码如下图所示:
select /*+ no_push_pred(b) */ a.table_name,b.cnt
from testtab a
left join (select object_name,count(*) cnt from testobj group by object_name) b
on a.table_name=b.object_name
where a.owner='DEMO';
此时,其执行计划如下图所示:
如上图所示,我们可以看到该执行计划中已经没有了“VIEW PUSHED PREDICATE”字样的步骤,其表示没有发生谓词推入。
而且,NO_PUSH_PRED的提示,也可以写入到内联视图中。比如下面这样:
select a.table_name,b.cnt
from testtab a
left join (select /*+ no_push_pred */ object_name,count(*) cnt from testobj group by object_name) b
on a.table_name=b.object_name
where a.owner='DEMO';
其也可以实现不使用谓词推入的效果。但这里需要注意,当把相关提示写入到内联视图内部时,不需要指定目标表名。
此时,得到的执行计划与写到主查询中的执行计划是完全一样的,如下图所示:
知识总结
1、NO_PUSH_PRED 提示是指导优化器不要将连接谓词推入到视图或出现在from子句中的子查询中。
2、当把NO_PUSH_PRED提示写入到内联视图中时,不需要提定目标表名。