在CHAR字段上建立函数索引后，不同执行计划下的结果集不同

LumenXH

在测试函数索引，发现在一个表的CHAR字段上建立函数索引之后，检索结果出现了变化

先建立个测试表，使用CHAR类型

create table test (
    a char(10),
    b char(10)
    );
insert into test values ('111','111');
insert into test values ('222','222');
commit;

SQL> select * from test;

A          B
---------- ----------
111        111
222        222

SQL> select * from test where a = '111';

A          B
---------- ----------
111        111

SQL> create index test_index on test (substr(a,1,1));

Index created

SQL> select * from test;

A          B
---------- ----------
111        111
222        222

SQL> select * from test where a = '111';

A          B
---------- ----------

困惑ing......
再试

SQL> select /*+ INDEX(test) */ * from test where a= '111';

A          B
---------- ----------

SQL> select /*+ FULL(test) */ * from test where a= '111';

A          B
---------- ----------
111        111

崩溃鸟。。。。。。


换成varchar2类型再看

drop table test;

create table test (
    a varchar2(10),
    b char(10)
    );
insert into test values ('111','111');
insert into test values ('222','222');
commit;

SQL> select * from test;

A          B
---------- ----------
111        111
222        222

SQL> select * from test where a = '111';

A          B
---------- ----------
111        111

SQL> create index test_index on test (substr(a,1,1));

Index created

SQL> select * from test;

A          B
---------- ----------
111        111
222        222

SQL> select /*+ INDEX(test) */ * from test where a= '111';

A          B
---------- ----------
111        111
SQL> select /*+ FULL(test) */ * from test where a= '111';

A          B
---------- ----------
111        111

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE        10.2.0.1.0        Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

再次败给oracle，这应该是个bug吧？

棉花糖ONE

Bug 6131467

LumenXH

原帖由 棉花糖ONE 于 2009-5-13 13:33 发表
Bug 6131467

果然，多谢棉花糖

yangtingkun

11.1.0.6上也有这个问题，似乎11.1.0.7解决这个bug，Oracle在11.2 fixed了这个bug。

Bug 5739162 - Wrong result when SUBSTR is used in functional index

yangtingkun

5739162 和 6131467 应该是同一个bug

LumenXH

原帖由 yangtingkun 于 2009-5-13 14:02 发表
11.1.0.6上也有这个问题，似乎11.1.0.7解决这个bug，Oracle在11.2 fixed了这个bug。

Bug 5739162 - Wrong result when SUBSTR is used in functional index

谢谢杨版

刚也查到有别的贴子讨论过这个问题，说测试了9.2.0.4没问题，10和11.1.0.6中都有问题

棉花糖ONE

问题其实并不是走函数索引出错了,根据谓词信息改写成如下sql就没问题

select * from (select * from test where substr(a,1,1)='1') where a='111';

棉花糖ONE

11.0.6也有这问题,刚才也测试过