深入理解Oracle索引(18)：函数索引的陷阱以及如何避免索引被污染

㈠ 函数索引的陷阱 使用函数索引一定要注意在函数代码变更后重建函数索引、否则、Oracle将返回错误结果但不给提示 测试如下： [plain] view plaincopyprint? SPANstyle=BACKGROUND-COLOR:rgb(102,102,102)hr@ORCLdroptabletpurge; Tabledropped. hr@ORCLcrea

     ㈠ 函数索引的陷阱
     
        使用函数索引一定要注意在函数代码变更后重建函数索引、否则、Oracle将返回错误结果但不给提示
     

        测试如下：

[plain] view plaincopyprint?

1. hr@ORCL> drop table t purge;  
2.   
3. Table dropped.  
4.   
5. hr@ORCL> create table t (x number,y varchar2(30));  
6.   
7. Table created.  
8.   
9. hr@ORCL> insert into t select rownum,rownum||'a' from dual connect by rownum
10.   
11. 999 rows created.  
12.   
13. hr@ORCL> ed  
14. Wrote file afiedt.buf  
15.   
16.   1  create or replace function f_david(p_value varchar2) return varchar2  
17.   2  deterministic is  
18.   3  begin  
19.   4  return p_value;  
20.   5* end;  
21.   6    
22.   7  /  
23.   
24. Function created.  
25.   
26. hr@ORCL> create index idx_f_david_t on t (f_david(y));  
27.   
28. Index created.  
29.   
30. hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);  
31.   
32. PL/SQL procedure successfully completed.  
33.   
34. hr@ORCL> select * from t where f_david(y)='8a';  
35.   
36.          X Y  
37. ---------- ------------------------------  
38.          8 8a  
39.   
40. hr@ORCL> ed         //ed是什么splplus命令？
41. Wrote file afiedt.buf  
42.   
43.   1  create or replace function f_david(p_value varchar2) return varchar2  
44.   2  deterministic is  
45.   3  begin  
46.   4  return p_value||'b';  
47.   5* end;  
48. hr@ORCL> /  
49.   
50. Function created.  
51.   
52. /* 此时的函数 f_david 已经不是我们所认识的那个了、但是查询依然如故！！！*/  
53. hr@ORCL> select * from t where f_david(y)='8a';  
54.   
55.          X Y  
56. ---------- ------------------------------  
57.          8 8a  
58.   
59.   
60. /* 索引重建查询没有记录、这才是我们要的正确结果*/  
61. hr@ORCL> drop index idx_f_david_t;  
62.   
63. Index dropped.  
64.   
65. hr@ORCL> create index idx_f_david_t on t (f_david(y));  
66.   
67. Index created.  
68.   
69. hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);  
70.   
71. PL/SQL procedure successfully completed.  
72.   
73. hr@ORCL> select * from t where f_david(y)='8a';  
74.   
75. no rows selected  

<span>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 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> <span><span><strong>exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);  //这句话什么意思？</strong>
</span>
</span>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</span>

     ㈡ 避免索引被污染
     
        这里给出 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            和
           start_time
     
     
                                                                 By David Lin 
                                                                 2013-06-06 
                                                                 Good Luck