发现一个SQL语句在RBO和CBO下居然返回不同 - Oracle专题深入讨论 - ITPUB论坛

yaanzy
高级会员

精华贴数 0
个人空间 0
技术积分 2335 (770)
社区积分 49 (5614)
注册日期 2001-12-20
论坛徽章:6

#1

使用道具

发表于 2004-12-9 15:01

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

创建测试用表：
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即使在执行计划差不多的情况下，真实运行似乎也是不同的

  请各位大侠指点指点

__________________

My Blog: http://blog.itpub.net/yaanzy

只看该作者

yaanzy
高级会员

精华贴数 0
个人空间 0
技术积分 2335 (770)
社区积分 49 (5614)
注册日期 2001-12-20
论坛徽章:6

#2

使用道具

发表于 2004-12-9 15:05

忘了说了，我的测试环境是：oracle 9.2.0.1 for windows

__________________

My Blog: http://blog.itpub.net/yaanzy

只看该作者

xzh2000
仙人抚我须结发授长生

精华贴数 13
个人空间 0
技术积分 46802 (16)
社区积分 5291 (329)
注册日期 2002-7-17
论坛徽章:31

#3

使用道具

发表于 2004-12-9 18:59

不明白你说的不同是结果不同还是执行计划不同？

__________________

过目即忘插柳成荫

只看该作者

yaanzy
高级会员

精华贴数 0
个人空间 0
技术积分 2335 (770)
社区积分 49 (5614)
注册日期 2001-12-20
论坛徽章:6

#4

使用道具

发表于 2004-12-10 08:59

当然是结果不同，一个报错，一个不报错

__________________

My Blog: http://blog.itpub.net/yaanzy

只看该作者

eygle
天下有雪

精华贴数 65
个人空间 0
技术积分 207428 (1)
社区积分 6461 (273)
注册日期 2001-10-8
论坛徽章:71

#5

使用道具

发表于 2004-12-10 10:42

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

QUOTE:

最初由 yaanzy 发布
创建测试用表：
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即使在执行计划差不多的情况下，真实运行似乎也是不同的

  请各位大侠指点指点

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

这个问题，你的猜测差不多了，原因就在于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天差地别，建议仔细读读相关文档!

__________________

提供数据库维护、培训、咨询服务

我的淘宝店: http://shop36913374.taobao.com

| 关于Eygle | 如何学习Oracle| 成就DBA生涯|

只看该作者

yaanzy
高级会员

精华贴数 0
个人空间 0
技术积分 2335 (770)
社区积分 49 (5614)
注册日期 2001-12-20
论坛徽章:6

#6

使用道具

发表于 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下，但实际上单独运行该子句是成功的

__________________

My Blog: http://blog.itpub.net/yaanzy

只看该作者

eygle
天下有雪

精华贴数 65
个人空间 0
技术积分 207428 (1)
社区积分 6461 (273)
注册日期 2001-10-8
论坛徽章:71

#7

使用道具

发表于 2004-12-10 12:15

恩，得纠正一下。

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

请看:

PHP code:



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  row

__________________

提供数据库维护、培训、咨询服务

我的淘宝店: http://shop36913374.taobao.com

| 关于Eygle | 如何学习Oracle| 成就DBA生涯|

只看该作者

yaanzy
高级会员

精华贴数 0
个人空间 0
技术积分 2335 (770)
社区积分 49 (5614)
注册日期 2001-12-20
论坛徽章:6

#8

使用道具

发表于 2004-12-10 13:31

多谢egyle的指点，强烈感谢！

__________________

My Blog: http://blog.itpub.net/yaanzy

只看该作者

li2
高级会员

精华贴数 0
个人空间 43
技术积分 4124 (389)
社区积分 1486 (851)
注册日期 2002-2-12
论坛徽章:5

#9

使用道具

发表于 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;

好像这么写有些傻,

__________________

君子终日乾乾，夕惕若，厉无咎当我沉默着的时候，我觉得充实；我将开口，同时感到空虚

只看该作者