查看: 223479|回复: 8

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

[复制链接]
论坛徽章:
7
ITPUB元老
日期:2005-04-22 09:33:06授权会员
日期:2005-10-30 17:05:33铁扇公主
日期:2006-04-13 11:04:44紫蜘蛛
日期:2006-04-13 11:05:58会员2006贡献徽章
日期:2006-04-17 13:46:34BLOG每日发帖之星
日期:2009-05-24 01:01:022010新春纪念徽章
日期:2010-03-01 11:08:29
发表于 2004-12-9 15:01 | 显示全部楼层 |阅读模式
创建测试用表:
create table aaa (a1 varchar2(10));
insert into aaa values ('2004-12-09');
insert into aaa values ('0000-00-00');
commit;

运行语句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即使在执行计划差不多的情况下,真实运行似乎也是不同的

  请各位大侠指点指点
论坛徽章:
7
ITPUB元老
日期:2005-04-22 09:33:06授权会员
日期:2005-10-30 17:05:33铁扇公主
日期:2006-04-13 11:04:44紫蜘蛛
日期:2006-04-13 11:05:58会员2006贡献徽章
日期:2006-04-17 13:46:34BLOG每日发帖之星
日期:2009-05-24 01:01:022010新春纪念徽章
日期:2010-03-01 11:08:29
 楼主| 发表于 2004-12-9 15:05 | 显示全部楼层
忘了说了,我的测试环境是:oracle 9.2.0.1 for windows

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
66
ITPUB元老
日期:2005-07-16 18:49:11授权会员
日期:2005-10-30 17:05:33ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44现任管理团队成员
日期:2011-05-07 01:45:08版主3段
日期:2012-05-15 15:24:11
发表于 2004-12-9 18:59 | 显示全部楼层
不明白你说的不同是结果不同还是执行计划不同?

使用道具 举报

回复
论坛徽章:
7
ITPUB元老
日期:2005-04-22 09:33:06授权会员
日期:2005-10-30 17:05:33铁扇公主
日期:2006-04-13 11:04:44紫蜘蛛
日期:2006-04-13 11:05:58会员2006贡献徽章
日期:2006-04-17 13:46:34BLOG每日发帖之星
日期:2009-05-24 01:01:022010新春纪念徽章
日期:2010-03-01 11:08:29
 楼主| 发表于 2004-12-10 08:59 | 显示全部楼层
当然是结果不同,一个报错,一个不报错

使用道具 举报

回复
论坛徽章:
117
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20版主7段
日期:2012-05-15 15:24:11ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:32紫蛋头
日期:2013-03-04 17:00:07优秀写手
日期:2013-12-18 09:29:09
发表于 2004-12-10 10:42 | 显示全部楼层

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

最初由 yaanzy 发布
[B]创建测试用表:
create table aaa (a1 varchar2(10));
insert into aaa values ('2004-12-09');
insert into aaa values ('0000-00-00');
commit;

运行语句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即使在执行计划差不多的情况下,真实运行似乎也是不同的

  请各位大侠指点指点 [/B]


这个问题也算是FAQ了。
首先纠正一点:
CBO和RBO的执行本就差异巨大,所以你说的:
所以基于规则的优化器RBO和CBO即使在执行计划差不多的情况下,真实运行似乎也是不同的[/COLOR]
是少见多怪了

这个问题,你的猜测差不多了,原因就在于a1 >'2'这个判断
对于RBO来说,SQL自右向左解析执行,所以在RBO下,以下语句:
a1 >'2'
     group by to_date(a1,'yyyy-mm-dd')

Oracle需要先对to_date(a1,'yyyy-mm-dd') 进行转换
那么遇到0000-00-00就抱错退出了。

在CBO下,Oracle会评估那个先执行成本更低。
由于你的a1是varchar2型,不需要进行类型转换,Oracle就可以比较出结果,所以先执行
而 to_date(a1,'yyyy-mm-dd') 要进行函数转换,Oracle选择后执行
这样a1 >'2' 首先生效,就过滤掉了0000-00-00。

所以就不抱错,正确执行了!

CBO与RBO天差地别,建议仔细读读相关文档!

使用道具 举报

回复
论坛徽章:
7
ITPUB元老
日期:2005-04-22 09:33:06授权会员
日期:2005-10-30 17:05:33铁扇公主
日期:2006-04-13 11:04:44紫蜘蛛
日期:2006-04-13 11:05:58会员2006贡献徽章
日期:2006-04-17 13:46:34BLOG每日发帖之星
日期:2009-05-24 01:01:022010新春纪念徽章
日期:2010-03-01 11:08:29
 楼主| 发表于 2004-12-10 11:28 | 显示全部楼层
首先多谢eygle的指点,但是我觉得你说法:
“     对于RBO来说,SQL自右向左解析执行,所以在RBO下,以下语句:
       a1 >'2'
      group by to_date(a1,'yyyy-mm-dd')

      Oracle需要先对to_date(a1,'yyyy-mm-dd') 进行转换
      那么遇到0000-00-00就抱错退出了。

值得商榷,按此推理,单独运行子句:
select to_date(a1,'yyyy-mm-dd') mon
from aaa where a1 >'2'
group by to_date(a1,'yyyy-mm-dd')
也应该报错,因为也是在RBO下,但实际上单独运行该子句是成功的

使用道具 举报

回复
论坛徽章:
117
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20版主7段
日期:2012-05-15 15:24:11ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:42ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:32紫蛋头
日期:2013-03-04 17:00:07优秀写手
日期:2013-12-18 09:29:09
发表于 2004-12-10 12:15 | 显示全部楼层
恩,得纠正一下。

RBO下自右向左解析执行没有问题,只不过group by的执行级别低于where condition 。

请看:

[php]
SQL> select to_date(a1,'yyyy-mm-dd') mon from aaa where a1 >'2' and to_date(a1,'yyyy-mm-dd') < sysdate;
ERROR:
ORA-01843: not a valid month

首先遇到to_date,退出


no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'AAA'




Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        383  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select to_date(a1,'yyyy-mm-dd') mon from aaa where to_date(a1,'yyyy-mm-dd') < sysdate and a1 >'2';

换一下位置,执行正确。

MON
---------
09-DEC-04


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'AAA'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
[/php]

使用道具 举报

回复
论坛徽章:
7
ITPUB元老
日期:2005-04-22 09:33:06授权会员
日期:2005-10-30 17:05:33铁扇公主
日期:2006-04-13 11:04:44紫蜘蛛
日期:2006-04-13 11:05:58会员2006贡献徽章
日期:2006-04-17 13:46:34BLOG每日发帖之星
日期:2009-05-24 01:01:022010新春纪念徽章
日期:2010-03-01 11:08:29
 楼主| 发表于 2004-12-10 13:31 | 显示全部楼层
多谢egyle的指点,强烈感谢!

使用道具 举报

回复
论坛徽章:
5
ITPUB元老
日期:2005-02-28 12:57:00授权会员
日期:2005-10-30 17:05:33蓝色妖姬
日期:2006-04-12 22:10:01会员2006贡献徽章
日期:2006-04-17 13:46:34
发表于 2004-12-10 14:32 | 显示全部楼层
再来一个简单的例子,rbo 和cbo  执行的次序是不一样的,所以有些数据库改了优化器工作方式应用程序可能就不能运行.

li2@gis(9.2.0)>CREATE TABLE LI2_TMP (A NUMBER,B NUMBER);

Table created.

li2@gis(9.2.0)>INSERT  INTO LI2_TMP (a,b) values (1,0);

1 row created.

Elapsed: 00:00:00.00
li2@gis(9.2.0)>INSERT  INTO LI2_TMP (a,b) values (0,1);

1 row created.

select /*+ rule */  * from li2_tmp where b>0 and a/b<>100
                                                  *
ERROR at line 1:
ORA-01476: divisor is equal to zero

li2@gis(9.2.0)>select /*+ first_rows */  * from li2_tmp where b>0 and a/b<>100;

         A          B
---------- ----------
         0          1
如果不使用hint 让语句在两种方式下都可以进行,就要使用
select   * from li2_tmp where b>0 and a/b<>100 and b>0;
要么
select * from (select * from li2_tmp where b>0) where a/b<>100;

好像这么写有些傻,

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表