CHAR型別函式索引導致結果錯誤

今天在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中描述了這個錯誤，Oracle在11.2和11.1.0.7中fixed了這個問題：

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