Oracle创建基于自定义函数的索引的问题
有一个人员表,和人员审核记录表
人员表与审核记录表是一对多的关系 现在需要用人员去查询审核记录的信息,条件是返回最低的审核状态
已经建立一个函数 用人员去查询 审核记录 但速度很慢 所以建立一个基于自定义函数的索引
但是创建的过程好像太慢 是什么原因呢 下面是部分代码
创建视图
create or replace view v_qy_ryshzt_test as
select f_findryshzt(a.id) fun,
(select dwmc from t_qy_qyjczl where dwbh=substr(f_findryshzt(a.id),3)) dwmc
,a.id,a.rybh,a.SFZH,a.xm,a.flag,a.submit,b.zclx,b.flag zcflag,a.dwbh
from T_QY_ZYJSRY a left join t_qy_zyjsryzcxx b on a.rybh=b.rybh and a.submit=1
union all
select (select dwmc from t_qy_qyjczl where dwbh=a.dwbh)dwmc,a.dwbh,a.id,a.rybh,a.SFZH,a.xm,a.flag,a.submit,b.zclx,b.flag zcflag
from T_QY_ZYJSRY a left join t_qy_zyjsryzcxx b on a.rybh=b.rybh and submit=0
创建函数
create or replace function f_findryshzt(ryid in varchar2)
return varchar2
DETERMINISTIC
is
PRAGMA AUTONOMOUS_TRANSACTION;
cursor cur_zt is
select sfcs||sffs||dwbh as zt from t_qy_dsh where bgxbh = ryid order by fsjbrq asc ;
lv_name varchar2(10);
num1 integer;
num2 integer;
num3 integer;
num integer;
dwbh1 varchar2(10);
dwbh2 varchar2(10);
dwbh3 varchar2(10);
dwbh varchar2(10);
begin
num:=0;
num1:=0;
num2:=0;
num3:=0;
dwbh:='';
dwbh1:='';
dwbh2:='';
dwbh3:='';
for l_test in cur_zt loop
lv_name := SUBSTR(l_test.zt,1,2) ;
if(lv_name='00') then
num3 := 3;
dwbh3 := SUBSTR(l_test.zt,3,(length(l_test.zt)-2));
elsif (lv_name='10') then
num2 := 2;
dwbh2 := SUBSTR(l_test.zt,3,(length(l_test.zt)-2));
elsif (lv_name='11') then
num1 := 1;
dwbh1 := SUBSTR(l_test.zt,3,(length(l_test.zt)-2));
end if;
end loop;
SELECT MAX(cc) INTO num FROM (
SELECT num1 AS cc FROM dual
UNION
SELECT num2 AS cc FROM dual
UNION
SELECT num3 AS cc FROM dual
) ;
if num=1 then
dwbh := '入库'||dwbh1;
elsif num=2 then
dwbh := '复审'||dwbh2;
elsif num=3 then
dwbh := '初审'||dwbh3;
else
dwbh := '';
end if;
return dwbh;
end f_findryshzt;
创建函数索引
create index t_ry_idx on t_qy_zyjsry (f_findryshzt (id))
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
自定义函数索引需要特定的系统参数配置。它需要在CBD模式下,并且设置query_rewrite_enabled=true和query_rewrite_integerity=trusted才能使优化器选择到函数索引 这个步骤应该怎么做???