--函数索引陷阱
--自定义函数使用函数索引要注意函数代码改变后的影响。
drop table t purge;
create table t ( x number, y varchar2(30));
set autotrace off
insert into t SELECT rownum, rownum||'a' FROM dual connect by rownum < 1000;
create or replace
package pkg_f is
function f(p_value varchar2) return varchar2 deterministic;
end;
/
create or replace
package body pkg_f is
function f(p_value varchar2) return varchar2
deterministic is
begin
return p_value;
end;
end;
/
create index idx_pkg_f_y on t ( pkg_f.f(y));
analyze table t compute statistics for table for all indexes for all indexed columns;
set autotrace on explain
SELECT * FROM t WHERE pkg_f.f(y)= '8a';
将包的代码修改如下:
create or replace
package body pkg_f is
function f(p_value varchar2) return varchar2
deterministic is
begin
return p_value||'b';
end;
end;
/
惊奇地发现查询出错误的值:
SELECT * FROM t WHERE pkg_f.f(y)= '8a';
在索引重建查询没有记录,这才是正确的结果:
drop index idx_pkg_f_y;
create index idx_pkg_f_y on t ( pkg_f.f(y));
--自定义函数使用函数索引要注意函数代码改变后的影响。
drop table t purge;
create table t ( x number, y varchar2(30));
set autotrace off
insert into t SELECT rownum, rownum||'a' FROM dual connect by rownum < 1000;
create or replace
package pkg_f is
function f(p_value varchar2) return varchar2 deterministic;
end;
/
create or replace
package body pkg_f is
function f(p_value varchar2) return varchar2
deterministic is
begin
return p_value;
end;
end;
/
create index idx_pkg_f_y on t ( pkg_f.f(y));
analyze table t compute statistics for table for all indexes for all indexed columns;
set autotrace on explain
SELECT * FROM t WHERE pkg_f.f(y)= '8a';
将包的代码修改如下:
create or replace
package body pkg_f is
function f(p_value varchar2) return varchar2
deterministic is
begin
return p_value||'b';
end;
end;
/
惊奇地发现查询出错误的值:
SELECT * FROM t WHERE pkg_f.f(y)= '8a';
在索引重建查询没有记录,这才是正确的结果:
drop index idx_pkg_f_y;
create index idx_pkg_f_y on t ( pkg_f.f(y));
SELECT * FROM t WHERE pkg_f.f(y)= '8a';
解析:当函数变更后,需要重建函数索引,不然查询结果不变。