ITPub博客

首页 > Linux操作系统 > Linux操作系统 > CHAR类型函数索引导致结果错误

CHAR类型函数索引导致结果错误

原创 Linux操作系统 作者:yangtingkun 时间:2009-05-13 21:48:25 0 删除 编辑

今天在PUB上看到了一个错误,在10.2.0.1上建立CHAR类型的SUBSTR函数索引后,可能导致随后的查询得到错误的结果:http://www.itpub.net/thread-1163703-1-1.html

 

 

测试发现问题在10.2.0.3中也存在:

SQL> CREATE TABLE T_CHAR (COL1 CHAR(5));

表已创建。

SQL> INSERT INTO T_CHAR VALUES ('111');

已创建 1 行。

SQL> CREATE INDEX IND_T_CHAR_COL1 ON T_CHAR (SUBSTR(COL1, 1, 1));

索引已创建。

SQL> SELECT * FROM T_CHAR;

COL1
-----
111

SQL> SELECT * FROM T_CHAR WHERE COL1 = '111';

未选定行

现在问题已经出现了,正常情况下是可以访问到记录的,看来是由于函数索引导致的问题:

SQL> SET AUTOT ON
SQL> SELECT * FROM T_CHAR WHERE COL1 = '111';

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 3849968283

-------------------------------------------------------------------------------------------
| Id  | Operation                   |Name           |Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID|T_CHAR         |    1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          |IND_T_CHAR_COL1|    1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"='111')
   2 - access(SUBSTR("COL1",1,1)='1')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        166  bytes sent via SQL*Net to client
        232  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> SELECT /*+ FULL(T_CHAR) */ * FROM T_CHAR WHERE COL1 = '111';

COL1
-----
111


执行计划
----------------------------------------------------------
Plan hash value: 2185307396

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     7 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_CHAR |     1 |     7 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"='111' AND SUBSTR("T_CHAR"."COL1",1,1)='1')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

当前的版本为10.2.0.3

SQL> SET AUTOT OFF
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

检查11g是否修正了这个bug

SQL> CONN YANGTK/YANGTK@YTK111
已连接。
SQL> SELECT * FROM V$VERSION;

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

SQL> CREATE TABLE T_CHAR (COL1 CHAR(5));

表已创建。

SQL> INSERT INTO T_CHAR VALUES ('111');

已创建 1 行。

SQL> SELECT * FROM T_CHAR WHERE COL1 = '111';

COL1
-----
111

SQL> CREATE INDEX IND_T_CHAR_COL1 ON T_CHAR(SUBSTR(COL1, 1, 1));

索引已创建。

SQL> SELECT * FROM T_CHAR WHERE COL1 = '111';

未选定行

SQL> SET AUTOT ON EXP
SQL> SELECT * FROM T_CHAR WHERE COL1 = '111';

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 1935535116

--------------------------------------------------------------------------------------------
| Id  | Operation                   |Name           |Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID|T_CHAR         |    1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          |IND_T_CHAR_COL1|    1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"='111')
   2 - access(SUBSTR("COL1",1,1)='1')

Note
-----
   - dynamic sampling used for this statement

11.1.0.6中居然也有这个错误。查询了一下metalink,在文档Doc ID:  5739162.8中描述了这个错误,Oracle11.211.1.0.7fixed了这个问题:

SQL> conn TEST/TEST@db01
已连接。
SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> CREATE TABLE T_CHAR (COL1 CHAR(5));

表已创建。

SQL> INSERT INTO T_CHAR VALUES ('111');

已创建 1 行。

SQL> SELECT * FROM T_CHAR WHERE COL1 = '111';

COL1
----------
111

SQL> CREATE INDEX IND_T_CHAR_COL1 ON T_CHAR(SUBSTR(COL1, 1, 1));

索引已创建。

SQL> SELECT * FROM T_CHAR WHERE COL1 = '111';

COL1
----------
111

SQL> SET AUTOT ON EXP
SQL> SELECT * FROM T_CHAR WHERE COL1 = '111';

COL1
----------
111


执行计划
----------------------------------------------------------
Plan hash value: 1935535116

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_CHAR          |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_CHAR_COL1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"='111')
   2 - access(SUBSTR("COL1",1,1)='1')

Note
-----
   - dynamic sampling used for this statement

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-596536/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle工具——WRAP
下一篇: M000进程失败
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10441099