函式索引陷阱

LuiseDalian發表於2014-01-16
函式索引

點選(此處)摺疊或開啟

  1. --自定義函式使用函式索引要注意函式程式碼改變後的影響。
  2. DROP TABLE t;
  3. CREATE TABLE t ( x NUMBER, y VARCHAR2(30));

  5. INSERT INTO t SELECT ROWNUM, ROWNUM || \'a\' FROM dual CONNECT BY ROWNUM < 1000;

  7. CREATE OR REPLACE PACKAGE pkg_f
  8. IS
  9.   FUNCTION f(p_value VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
  10. END;
  11. /

  13. create or REPLACE package body pkg_f
  14. is
  15.   function f(p_value varchar2) return VARCHAR2 deterministic is
  16.   begin
  17.     return p_value;
  18.   end;
  19. end;
  20. /

  22. create index idx_pkg_f_y on t ( pkg_f.f(y));
  23. analyze table t compute statistics for table for all indexes for all indexed columns;
  24. set autotrace on explain
  25. SELECT * FROM t WHERE pkg_f.f(y)= \'8a\';

  27.          X Y
  28. ---------- ------------------------------
  29.            8 8a



點選(此處)摺疊或開啟

  1. --修改包的定義
  2. create or REPLACE package body pkg_f
  3. is
  4.   function f(p_value varchar2) return VARCHAR2 deterministic
  5.   is
  6.   begin
  7.     return p_value||\'b\';
  8.   end;
  9. end;
  10. /

  12. --驚奇地發現查詢出錯誤的值：
  13. SELECT * FROM t WHERE pkg_f.f(y)= \'8a\';

  15.          X Y
  16. ---------- ------------------------------
  17.            8 8a



點選(此處)摺疊或開啟

  1. --重建索引,再次查詢,得到正確的結果
  2. drop index idx_pkg_f_y;
  3. create index idx_pkg_f_y on t ( pkg_f.f(y));
  4. SELECT * FROM t WHERE pkg_f.f(y)= \'8a\';

  6. luise@TESTDB11>SELECT * FROM t WHERE pkg_f.f(y)= \'8a\';

  8. no rows selected


--這裡使用的是包中定義的函式，獨立函式也是如此。

--結論：使用函式索引一定要注意在函式程式碼修改後,一定要重建函式索引.

--      否則Oracle就會使用原來的函式索引查詢出錯誤的結果,而沒有任何提示

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1071280/，如需轉載，請註明出處，否則將追究法律責任。

相關文章