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

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

  请各位大侠指点指点

yaanzy

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

xzh2000

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

yaanzy

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

eygle

最初由 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天差地别，建议仔细读读相关文档!

yaanzy

首先多谢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下，但实际上单独运行该子句是成功的

eygle

恩，得纠正一下。

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]

yaanzy

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

li2

再来一个简单的例子,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;

好像这么写有些傻,