第一章 问题引入分析

在分析客户环境的一条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转换成内连接的写法，才顺利的实现了谓词推入。

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