自定义函数使用函数索引要注意函数代码改变后的影响

最新推荐文章于 2022-04-20 19:01:27 发布

cnf46338

最新推荐文章于 2022-04-20 19:01:27 发布

阅读量135

点赞数

结论：自定义函数上建有索引，当自定义函数代码发生变化后，原自定义函数上建有索引查询会产生错误的结果，只有重建索引
SQL> create or replace
  2  package body pkg_f is
  3  function f(p_value varchar2) return varchar2
  4  deterministic is
  5  begin
  6  return p_value;
  7  end;
  8  end;
  9  /
Package body created
Executed in 0.047 seconds

SQL>
SQL> create index idx_pkg_f_y on t ( pkg_f.f(y));
Index created
Executed in 0.062 seconds
SQL> analyze table t compute statistics for table for all indexes for all indexed columns;
Table analyzed
Executed in 0.062 seconds
SQL> set autotrace on explain
Cannot SET AUTOTRACE
SQL> SELECT * FROM t WHERE pkg_f.f(y)= '8a';
      X Y
---------- ------------------------------
      8 8a
Executed in 0.032 seconds

SQL>
将包的代码修改如下：
SQL> create or replace
  2  package body pkg_f is
  3  function f(p_value varchar2) return varchar2
  4  deterministic is
  5  begin
  6  return p_value||'b';
  7  end;
  8  end;
  9  /
Package body created
Executed in 0.046 seconds
发现查询出错误的值：
SQL> SELECT * FROM t WHERE pkg_f.f(y)= '8a';
      X Y
---------- ------------------------------
      8 8a
Executed in 0.031 seconds

SQL>
索引重建后这才是正确的结果
SQL> drop index idx_pkg_f_y;
Index dropped
Executed in 0.078 seconds
SQL> create index idx_pkg_f_y on t ( pkg_f.f(y));
Index created
Executed in 0.031 seconds
SQL> SELECT * FROM t WHERE pkg_f.f(y)= '8a';
      X Y
---------- ------------------------------
Executed in 0.016 seconds

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12519248/viewspace-1284600/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12519248/viewspace-1284600/