欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-06-12 14:45:54
...

㈠ 函数索引的陷阱 使用函数索引一定要注意在函数代码变更后重建函数索引、否则、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. Table dropped.
  3. hr@ORCL> create table t (x number,y varchar2(30));
  4. Table created.
  5. hr@ORCL> insert into t select rownum,rownum||'a' from dual connect by rownum
  6. 999 rows created.
  7. hr@ORCL> ed
  8. Wrote file afiedt.buf
  9. 1 create or replace function f_david(p_value varchar2) return varchar2
  10. 2 deterministic is
  11. 3 begin
  12. 4 return p_value;
  13. 5* end;
  14. 6
  15. 7 /
  16. Function created.
  17. hr@ORCL> create index idx_f_david_t on t (f_david(y));
  18. Index created.
  19. hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);
  20. PL/SQL procedure successfully completed.
  21. hr@ORCL> select * from t where f_david(y)='8a';
  22. X Y
  23. ---------- ------------------------------
  24. 8 8a
  25. hr@ORCL> ed //ed是什么splplus命令?
  26. Wrote file afiedt.buf
  27. 1 create or replace function f_david(p_value varchar2) return varchar2
  28. 2 deterministic is
  29. 3 begin
  30. 4 return p_value||'b';
  31. 5* end;
  32. hr@ORCL> /
  33. Function created.
  34. /* 此时的函数 f_david 已经不是我们所认识的那个了、但是查询依然如故!!!*/
  35. hr@ORCL> select * from t where f_david(y)='8a';
  36. X Y
  37. ---------- ------------------------------
  38. 8 8a
  39. /* 索引重建查询没有记录、这才是我们要的正确结果*/
  40. hr@ORCL> drop index idx_f_david_t;
  41. Index dropped.
  42. hr@ORCL> create index idx_f_david_t on t (f_david(y));
  43. Index created.
  44. hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);
  45. PL/SQL procedure successfully completed.
  46. hr@ORCL> select * from t where f_david(y)='8a';
  47. no rows selected