ITPub博客

首页 > 数据库 > 数据库开发技术 > 发现一个SQL语句在RBO和CBO下居然返回不同结果

发现一个SQL语句在RBO和CBO下居然返回不同结果

原创 数据库开发技术 作者:yaanzy 时间:2004-12-10 13:48:33 0 删除 编辑
创建测试用表:
create table aaa (a1 varchar2(10));
insert into aaa values ('2004-12-09');
insert into aaa values ('0000-00-00');
commit;[@more@]

运行语句1:
select  * from
 ( select a1 mon from aaa) c ,
 ( select to_date(a1,'yyyy-mm-dd') mon
     from aaa  where  a1 >'2'
     group by to_date(a1,'yyyy-mm-dd') ) d
where d.mon < sysdate;

报错:ERROR 位于第3行:
     ORA-01843: 无效的日期

执行计划如下:
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     VIEW
   3    2       SORT (GROUP BY)
   4    3         TABLE ACCESS (FULL) OF 'AAA'
   5    1     TABLE ACCESS (FULL) OF 'AAA'

同样的语句加上提示,并且使提示后的执行路径与上面的基本一样:
语句2:
select /*+ use_nl(c,d) */ * from
 ( select a1 mon from aaa) c ,
 ( select to_date(a1,'yyyy-mm-dd') mon
     from aaa  where  a1 >'2'
     group by to_date(a1,'yyyy-mm-dd') ) d
where d.mon < sysdate;

正确执行,输出如下:
MON        MON
---------- -----------------
2004-12-09 20041209 00:00:00
0000-00-00 20041209 00:00:00


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=82 Bytes=1066)

   1    0   NESTED LOOPS (Cost=6 Card=82 Bytes=1066)
   2    1     VIEW (Cost=4 Card=1 Bytes=6)
   3    2       SORT (GROUP BY) (Cost=4 Card=1 Bytes=7)
   4    3         TABLE ACCESS (FULL) OF 'AAA' (Cost=2 Card=1 Bytes=7)
   5    1     TABLE ACCESS (FULL) OF 'AAA' (Cost=2 Card=82 Bytes=574)

困惑1:语句1为什么报错?
      因为在c子句中我已经用a1 >'2'条件将数据中的0000-00-00给过滤了,而且单独执行c子句正常
      根据报错信息好像对数据'0000-00-00'进行了转化工作;

困惑2:语句2中加上提示就正常,似乎优化器使用CBO就正常,我使用其它几个提示:
      use_hash use_merge 或者只用 ordered(c,d)都返回正常;
      我还试过analyze表,这时语句1就正确了
     
      所以基于规则的优化器RBO和CBO即使在执行计划差不多的情况下,真实运行似乎也是不同的

解答:对于RBO来说,SQL自右向左解析执行,语句:
     select  * from
         ( select a1 mon from aaa) c ,
         ( select to_date(a1,'yyyy-mm-dd') mon
             from aaa  where  a1 >'2'
            group by to_date(a1,'yyyy-mm-dd') ) d
      where d.mon < sysdate;
      sql解析后最外围的d.mon < sysdate被放进d子句中:
      select  * from
         ( select a1 mon from aaa) c ,
         ( select to_date(a1,'yyyy-mm-dd') mon
             from aaa  where  a1 >'2' and to_date(a1,'yyyy-mm-dd') < sysdate
            group by to_date(a1,'yyyy-mm-dd') ) d;
           
      在RBO中to_date(a1,'yyyy-mm-dd') < sysdate 比 a1 >'2'先执行,所以子句d就不能
      运行成功了;
      在CBO中Oracle会评估那个先执行成本更低,由于a1是varchar2型,不需要进行类型转换Oracle
      就可以比较出结果,所以先执行而to_date(a1,'yyyy-mm-dd')要进行函数转换,Oracle选择后
      执行,这样a1 >'2' 首先生效,就过滤掉了0000-00-00,正确执行;
 
 详见itpub上讨论:http://www.itpub.net/299189.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3898/viewspace-783884/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    108
  • 访问量
    759573