深入理解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;
3. Table dropped.
5. hr@ORCL> create table t (x number,y varchar2(30));
7. Table created.
9. hr@ORCL> insert into t select rownum,rownum||’a’ from dual connect by rownum<1000;
11. 999 rows created.
13. hr@ORCL> ed
14. Wrote file afiedt.buf
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 /
24. Function created.
26. hr@ORCL> create index idx_f_david_t on 本文来源gaodai$ma#com搞$代*码网2t (f_david(y));
28. Index created.
30. hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>’HR’,tabname=>’T’,estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);
32. PL/SQL procedure successfully completed.
34. hr@ORCL> select * from t where f_david(y)=’8a’;
36. X Y
37. ———- ——————————
38. 8 8a
40. hr@ORCL> ed //ed是什么splplus命令？
41. Wrote file afiedt.buf
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> /
50. Function created.
52. /* 此时的函数 f_david 已经不是我们所认识的那个了、但是查询依然如故！！！*/
53. hr@ORCL> select * from t where f_david(y)=’8a’;
55. X Y
56. ———- ——————————
57. 8 8a
60. /* 索引重建查询没有记录、这才是我们要的正确结果*/
61. hr@ORCL> drop index idx_f_david_t;
63. Index dropped.
65. hr@ORCL> create index idx_f_david_t on t (f_david(y));
67. Index created.
69. hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>’HR’,tabname=>’T’,estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);
71. PL/SQL procedure successfully completed.
73. hr@ORCL> select * from t where f_david(y)=’8a’;
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 edWrote 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 8ahr@ORCL> edWrote 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 < sysdate;
和
start_time < sysdate – 7

By David Lin
2013-06-06
Good Luck

喜欢 (0)赏

[搞代码]

分享 (0)