[转载][DataBase][Oracle]10G有关SUBSTR函数索引的bug
转载自: http://www.validba.net/2009/05/10g%E6%9C%89%E5%85%B3substr%E5%87%BD%E6%95%B0%E7%B4%A2%E5%BC%95%E7%9A%84bug/
目的: 防止网页删除或博客关闭找不到
------------------------------------------------------------------------------------------------
10G有关SUBSTR函数索引的bug
今天在itpub上看见一个帖子http://www.itpub.net/thread-1163703-1-1.html,有关在char列上用substr创建函数索引的问题.最终结果是char上的函数索引在查询中走索引执行计划时,执行结果错误。下面的测试:
测试数据:
create table t(a char(3),b varchar2(3));
insert into t values('111','111');
commit;
create index t_a on t(substr(a,1,1));
create index t_b on t(substr(b,1,1));
exec dbms_stats.gather_table_stats(USER,'t',cascade=>true);
测试一:
数据库版本:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
测试结果:
SQL> select * from t where a=’111′;
no rows selected
Execution Plan
———————————————————-
Plan hash value: 1344327818
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_A | 1 | | 1 (0)| 00:00:01 |
————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(“A”=’111′)
2 – access(SUBSTR(“A”,1,1)=’1′)
SQL> select * from t where b=’111′;
A b
—— ——
111 111
Execution Plan
———————————————————-
Plan hash value: 3270541362
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_B | 1 | | 1 (0)| 00:00:01 |
————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(“B”=’111′)
2 – access(SUBSTR(“B”,1,1)=’1′)
从测试的结果看来,在char上利用substr创建的函数索引,执行计划是正确的,但结果不正确,varchar2的执行计划和执行结果都是正确的.从执行计划上看来oracle在走索引时,选择的路径都对了, 过滤数据时条件也是对的access(SUBSTR(“A”,1,1)=’1′) 、access(SUBSTR(“B”,1,1)=’1′)唯一不同的是char类型的函数索引没有返回数据,得出了一个错误的执行结果.
测试二:
数据库版本:
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for Linux: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production
测试结果:
SQL> select * from t where b=’111′;
A B
— —
111 111
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=8)
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (TABLE) (Cost=1 Card=
1 Bytes=8)
SQL> select * from t where a=’111′;
no rows selected
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=8)
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (TABLE) (Cost=1 Card=
1 Bytes=8)
2 1 INDEX (RANGE SCAN) OF ‘T_A’ (INDEX) (Cost=1 Card=1)
跟10.2的版本相同,所以看来这个问题在10.1、10.2中都存在的.
meatlink 上 Doc ID: 5739162.8 已经确认这个问题是个bug ,bug id :5739162。 fixed:10.2.0.5 、11.2、11.1.0.7.