oracle子查询 导致无法谓词推入的研究 -m6米乐安卓版下载

在分析客户环境的一条sql时，发现了无法顺利谓词推入的现象。故此对案例做了进一步模拟及测试。以确定问题原因。数据库版本:oracle 11g。

select 。。。 --省略部分
  from (select 
               。。。 --省略部分          
from t_a a
        union all
        select 
               。。。 --省略部分          
from t_b rc) cc
 where exists (select 1
          from t
         where t.case_id = cc.case_id
           and t.code = 'xxxxxxxxxxxxx');

其中，子查询中的code列具有很好的过滤条件，返回行数很少。且对应视图cc中相关表的关联列case_id也包含索引。在这种情况下，个人理解优化器应该是可以顺利的将外面exists子查询的关联列推入到视图cc内的，从而通过访问cc的索引来避免视图cc中的两次全表扫描访问的。

而实际事与愿违，不论如何添加hint，都无法推入关联条件到cc视图内。总是通过全表扫描访问大表。造成性能问题。也自己检查了满足谓词推入的条件。确定当前场景下理应可以谓词推入：

抛开连接方式不谈，首先cc视图就是包含union/all类的，是满足谓词推入的前提条件的。

基于上述问题现象，设计了如下测试脚本：以供后续实验进一步定位问题：

create table szt.t1co as select * from dba_objects;
create table szt.t2co as select * from dba_objects;
create table szt.t1 as select * from dba_objects;
create index szt.idx_t2co_id on szt.t2co(object_id);
create index szt.idx_t1co_id on szt.t1co(object_id);
create index szt.idx_t1_data on szt.t1(data_object_id);
 exec dbms_stats.gather_table_stats(ownname=>'szt',tabname=>'t1co');
 exec dbms_stats.gather_table_stats(ownname=>'szt',tabname=>'t2co');
 exec dbms_stats.gather_table_stats(ownname=>'szt',tabname=>'t1');

测试语句：

select count(*)
  from (select object_id, object_name, data_object_id
          from t2co
        union all
        select object_id, object_name, data_object_id
          from t1co) cc
 where exists (select 1
          from t1
         where data_object_id = '46'
           and t1.object_id = cc.object_id);

对应的执行计划输出如下：

可以看到，尽管t1表预估与实际返回行数仅为1，但对于希望出现的谓词推入到cc视图的动作，仍然没有出现。与原始问题现象保持一致，两次全表访问大表，造成性能问题。

基于上一章节发现的性能问题，尝试做了几种结构相似的改写，以确定无法谓词推入的问题是否还存在。也设计了可以做谓词推入的三种类似视图。让测试结果更全面。

2.1 内连接写法

由于exists关联子查询只是为了与cc视图做半连接，可以等价的将exists子查询改写为如下类别语句：
t1子查询去重后与原始的exists子查询完全等价。

2.1.1 union视图

select count(*)
  from (select object_id, object_name, data_object_id
          from t2co
        union all
        select object_id, object_name, data_object_id
          from t1co) cc,
(select distinct t1.object_id
          from t1
         where data_object_id = '46') t1
 where t1.object_id = cc.object_id ;

执行计划如下：

由于改写为内连接形式，顺利实现了谓词推入，这才是我们希望看到的高效查询。

2.1.2 distinct视图

将cc视图改写为distinct视图。

select count(*)
  from (select distinct object_id, object_name, data_object_id
          from t1co) cc,
(select distinct t1.object_id
          from t1
         where data_object_id = '46') t1
 where t1.object_id = cc.object_id ;

执行计划如下：

同样做了谓词推入。

2.1.3 group by视图

将cc视图改写为group by视图。

select count(*)
  from (select object_id, object_name, data_object_id,count(*) cnt
          from t1co
		  group by object_id, object_name, data_object_id) cc,
(select distinct t1.object_id
          from t1
         where data_object_id = '46') t1
 where t1.object_id = cc.object_id ;

执行计划如下：

同样做了谓词推入。

2.2 exists关联子查询

union视图上面已经测试过，无法谓词推入，这里不再测试。

2.2.1 distinct视图
将cc视图改写为distinct视图。

select count(*)
  from (
        select  distinct object_id, object_name, data_object_id
          from t1co) cc
 where exists (select 1
          from t1
         where data_object_id = '46'
           and t1.object_id = cc.object_id);

执行计划如下：

尽管将视图调整为distinct。但谓词推入技术仍没有出现，访问大表造成性能问题。

2.2.2 group by视图

将cc视图改写为group by视图。

select count(*)
  from (
        select object_id, object_name, data_object_id,count(*)
          from t1co
		  group by object_id, object_name, data_object_id) cc
 where exists (select 1
          from t1
         where data_object_id = '46'
           and t1.object_id = cc.object_id);

执行计划如下：

尽管将视图调整为group by。但谓词推入技术仍没有出现，访问大表造成性能问题。

2.3 in子查询

exists子查询是可以等价改写为in子查询的。这里继续测试。

2.3.1 union视图

select count(*)
  from (select object_id, object_name, data_object_id
          from t2co
        union all
        select object_id, object_name, data_object_id
          from t1co) cc
 where cc.object_id in (select t1.object_id 
          from t1
         where data_object_id = '46');

执行计划如下：

通过in子查询写法，仍然无法完成谓词推入。

2.3.2 distinct视图

将cc视图改写为distinct视图。

select count(*)
  from (
        select  distinct object_id, object_name, data_object_id
          from t1co) cc
 where cc.object_id in (select t1.object_id 
          from t1
         where data_object_id = '46');

执行计划如下：

尽管将视图调整为distinct。但谓词推入技术仍没有出现，访问大表造成性能问题。

2.3.3 group by视图

将cc视图改写为group by视图。

select count(*)
  from (
        select object_id, object_name, data_object_id,count(*)
          from t1co
		  group by object_id, object_name, data_object_id) cc
 where cc.object_id in (select t1.object_id 
          from t1
         where data_object_id = '46');

执行计划如下：

尽管将视图调整为group by。但谓词推入技术仍没有出现，访问大表造成性能问题。

经过上述三种场景，九个实验的测试过程可知：
只有与视图间是直接连接的写法，可以顺利实现谓词推入。而对于使用了子查询做主要过滤条件的写法，都不能完成谓词推入。这里分析可能优化器在对子查询做子查询展开后，并没有对能否进一步做谓词推入做比较全面的尝试，导致谓词推入失败。

为了进一步分析问题。尝试对union视图的查询做了10053分析。
其中不能谓词推入的exists写法，是有如下信息：

没有发现有效的连接条件；
没有发现可将谓词推入的有效视图。

这里看到优化器也是尝试做了谓词推入的尝试，但没有成功。

而通过连接写法的视图，其10053中是可以顺利找到谓词推入视图的：

这里看到，成功的找到可以谓词推入的条件，并将谓词推入到了视图内部。

通过本篇的实验，告诉我们在sql中的主要过滤条件如果在子查询中，如果还希望将连接条件推入到外部视图中去。是无法顺利完成的。因此就需要我们在编写sql的时候，对这种场景进行分析。避免通过子查询完成主要的过滤条件。

以上测试仅是在oracle 11g环境完成的，不排除随着版本的升级，oracle优化器可能会修复上述明显的问题缺陷。

因此又在12c的场景下做了简单测试。发现只有in子查询及union视图的组合，才可以顺利推入，对于其他写法及视图的组合，仍然不能谓词推入。

查看10053信息：
st: query in kkqstardrv:******* unparsed query is *******

可以看到12c环境也仅是把目标sql转换成内连接的写法，才顺利的实现了谓词推入。

这里也可以看出，优化器找到了可以谓词推入的条件，从而顺利的推入。