测试如下：

hr@ORCL> drop table t purge;

Table dropped.

hr@ORCL> create table t (x number,y varchar2(30));

Table created.

hr@ORCL> insert into t select rownum,rownum||\'a\' from dual connect by rownum<1000;

999 rows created.

hr@ORCL> ed
Wrote file afiedt.buf

  1  create or replace function f_david(p_value varchar2) return varchar2
  2  deterministic is
  3  begin
  4  return p_value;
  5* end;
  6  
  7  /

Function created.

hr@ORCL> create index idx_f_david_t on t (f_david(y));

Index created.

hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>\'HR\',tabname=>\'T\',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);

PL/SQL procedure successfully completed.

hr@ORCL> select * from t where f_david(y)=\'8a\';

         X Y
---------- ------------------------------
         8 8a

hr@ORCL> ed
Wrote file afiedt.buf

  1  create or replace function f_david(p_value varchar2) return varchar2
  2  deterministic is
  3  begin
  4  return p_value||\'b\';
  5* end;
hr@ORCL> /

Function created.

/* 此时的函数 f_david 已经不是我们所认识的那个了、但是查询依然如故！！！*/
hr@ORCL> select * from t where f_david(y)=\'8a\';

         X Y
---------- ------------------------------
         8 8a


/* 索引重建查询没有记录、这才是我们要的正确结果*/
hr@ORCL> drop index idx_f_david_t;

Index dropped.

hr@ORCL> create index idx_f_david_t on t (f_david(y));

Index created.

hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>\'HR\',tabname=>\'T\',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);

PL/SQL procedure successfully completed.

hr@ORCL> select * from t where f_david(y)=\'8a\';

no rows selected

     ㈡ 避免索引被污染
     
        这里给出 2 条意见、
       
        ① 不要在字段前增加函数
          
           如：
           to_char(start_time,\'yyyy.mm.dd\') between \'2013.06.06\' and \'2013.06.10\'
           和
           start_time between to_date(\'2013.06.06\',\'yyyy.mm.dd\') and to_date(\'2013.06.10\',\'yyyy.mm.dd\')
          
           任何时候都应该是第二种！！！
          
        ② 不要把字段嵌入到表达式中
          
           如：
           start_time + 7 < sysdate;
           和
           start_time < sysdate - 7
     
     
                                                                 By David Lin 
                                                                 2013-06-06 
                                                                 Good Luck