oracle 建立函数索引出现的问题，大家帮忙

nomad88

表结构：
create table ZHB
(
  A CHAR(10),
  B CHAR(10)
)
数据：
a         b
111   222
333   444

建立一个函数索引：
create index ZHB01 on ZHB (SUBSTR(A,1,1))；
执行查询：
select * from zhb where a = '111'
no rows selected
select * from zhb where a like '1%'，可以得到正确的查询结果。
如果再增加一个索引：
create index ZHB02 on ZHB (A)；
执行查询：select * from zhb where a = '111'，就可以得到正确结果。
为什么会这样？单独使用函数索引，用“=”来查询就查不出记录？各位帮忙看一下，谢谢了

nomad88

顶一下，各位帮帮忙。。。。

luffy.deng

你肯定你说的？

nomad88

肯定，版本aix5.3
oracle版本 10.2.0.1.0，但是在linux上同样的oracle版本就不会出现问题

nomad88

4楼的碰到过类似的问题吗？我看了执行计划了，也看不出来什么原因

imtj

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

未选定行


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

--------------------------------------------------------------------------------
-----

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
    |

--------------------------------------------------------------------------------
-----

|   0 | SELECT STATEMENT            |       |     1 |    24 |     1   (0)| 00:00
:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| ZHB   |     1 |    24 |     1   (0)| 00:00
:01 |

|*  2 |   INDEX RANGE SCAN          | ZHB01 |     1 |       |     1   (0)| 00:00
:01 |

--------------------------------------------------------------------------------
-----


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

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

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


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        318  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
从以上可以看到这个查询走了索引，按照理论上面来说这个查询不应该走索引，而应该是全表扫描所以造成了结果错误
修复这个错误就是改变这个执行计划，不走索引
我们使用
analyze table zhb compute statistic
select * from zhb where a='111'
A          B
---------- ----------
111        222
结果正确，查看执行计划为
SQL> select * from zhb where a='111';


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

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

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

   1 - filter("A"='111' AND SUBSTR("ZHB"."A",1,1)='1')


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

nomad88

7楼的aix和oracle的版本是多少呢？我按你的说法做了执行计划，还是查不出来结果。
让同事在hp+oracle机器上试了，和我的情况一样。
另外找了2台aix5.3+oracle10.2.0.1，一台和我情况一样，另外一台可以查出结果

blue_stone

原帖由 nomad88 于 2008-8-18 10:19 发表
7楼的aix和oracle的版本是多少呢？我按你的说法做了执行计划，还是查不出来结果。
让同事在hp+oracle机器上试了，和我的情况一样。
另外找了2台aix5.3+oracle10.2.0.1，一台和我情况一样，另外一台可以查出结果

貌似Oracle的bug， 不能确定。

联系oracle support看看？

[ 本帖最后由 blue_stone 于 2008-8-18 11:42 编辑 ]

nomad88

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced