关于CBO谓词推入的八阿哥

浏览: 2923

版本:oracle 11gr2

背景

今天群里有朋友发了个神奇的SQL:

注释部分是数据量,两段SQL除了union和union all外,其他部分一模一样(第一段是union all,第二段是union)

但是结果却是:

union all ---1765条数据

union     ---2014条数据

 ---1765
SELECT count(DISTINCT(bankid)) FROM ( 
SELECT m.BUYERID  AS bankid FROM v_product_deal_main m ,base_member b WHERE b.id=m.BUYERID ANDm.DEALDATE <= to_date('20160630','yyyymmdd')AND m.STATE=9AND b.state=0UNION ALL
SELECT m.sellerID  AS bankid FROM v_product_deal_main m ,base_member b WHERE b.id=m.sellerid ANDm.DEALDATE <= to_date('20160630','yyyymmdd')AND m.STATE=9AND b.state=0
)--2014
SELECT count(DISTINCT(bankid)) FROM ( 
SELECT m.BUYERID  AS bankid FROM v_product_deal_main m ,base_member b WHERE b.id=m.BUYERID ANDm.DEALDATE <= to_date('20160630','yyyymmdd')AND m.STATE=9AND b.state=0UNION 
SELECT m.sellerID  AS bankid FROM v_product_deal_main m ,base_member b WHERE b.id=m.sellerid ANDm.DEALDATE <= to_date('20160630','yyyymmdd')AND m.STATE=9AND b.state=0

已知第二段SQL(union)的结果是正确的数据量

于是这位少年就把第一段SQL改写了一下,测试问题在哪里

第一段:

Clipboard Image.png

第二段:

Clipboard Image.png

很明显:

第一段bankid = 3放入SQL内是有数据的

第二段bankid = 3放到SQL外面,且union all了一段SQL居然没数据?

定位开始

一. 别名一致问题

由于这个SQL里的两个unionall别名是一样的,都是M和B,所以改了下别名:

Clipboard Image.png

改成了S和X,但是发现依旧没数据

Clipboard Image.png

我注意到from的表名是有个V_的,所以问下是不是视图,答曰“是”

二. 执行计划

由于是视图就怀疑是不是视图合并引起的

同时这位同学说上线两个SQL颠倒下,就有了数据!

看了下执行计划,颠倒前后的SQL执行计划是一样的,于是怀疑是谓词推入导致,于是自己写了个测试脚本重现问题:

测试脚本(在scott用户下):

create table emp_copy as  select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp;create table emp_copy1 as  select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp_copy;create or replace view emp_v as
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp_copyunion all
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp_copy1;
 
select * from
(
select e.sal as sal from emp_v e,dept d
where e.deptno = d.deptno
 and e.sal > 1000        -----A
union all
select distinct e.sal as sal fromemp_v e, dept d
where e.deptno = d.deptno -----B
)
where sal < 1000;  

根据SQL可以看出:

A部分的SQL由于有e.sal > 1000这个条件,与子查询外部的sal < 1000是相互冲突的,所以是不会有数据的。

B部分的SQL则没有e.sal > 1000这个条件,与子查询外部的sal < 1000是不冲突的,所以应该有数据。

 

让我们来分别看下A在上和B在上,这两个SQL的执行计划有什么不同(按理说应该是相同的)。

子查询中A在B之上的SQL执行计划:

Clipboard Image.png

从Filter predicates部分可以看出sal < 1000的条件只推送到给了A,而没有给B,甚至执行计划中,根本没有B的执行迹象。

子查询中B在A之上的SQL执行计划:

Clipboard Image.png

可以看到Filter predicates部分可以看出sal < 1000的条件全部推送了进去,且AB两部分SQL都有执行。

问题解决

发现这个问题后,让这位同学给子查询加了个别名t,再增加一个hint :/*+ NO_PUSH_PRED(t) */

希望通过禁止谓词的推入来限制因此出现的问题,但是发现hint并没有起作用,于是修改SQL为with as

with t as
(
select e.sal as sal from emp_v e, dept d
where e.deptno = d.deptno
  ande.sal > 1000        -----A
union all
select distinct e.sal as sal from emp_v e,dept d
where e.deptno = d.deptno -----B
)
select * from  t
where t.sal < 1000;  

Clipboard Image.png

可以看到即使A在B之上,也依旧可以正常的执行,并输出正确的结果。

当,且仅当如下条件同时满足时,才会发生上述BUG:

  1. 查询语句中有子查询
  2. 子查询中存在union all(union则不会出现问题)
  3. 子查询中的SQL都有相同的普通视图
  4. 普通视图中存在union all
  5. 不满足where条件的SQL(例子中的A)为子查询第一段SQL时

只要不满足其中一个条件就不会出现这个问题。

经过测试发现: 增加/*+ rule*/的hint不会出现错误情况,所以推测这是CBO的一个小bug

持续验证

12cr1版本的Oracle不存在此问题

Clipboard Image.png

推荐 11
本文由 老头子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

2 个评论

修改测试脚本为:
select * from
(select e.sal as sal from emp_v e, dept d
where e.deptno = d.deptno
and e.deptno <> 20
and e.sal > 1000
union all
select distinct e.sal as sal from emp_v e, dept d
where e.deptno = d.deptno
)
where sal < 1000;
好详细的说,我也遇到BUG的,需要升级。不过不是这个。

要回复文章请先登录注册