基于函数的索引

有时可能要在 where 字句中使用表达式.如果在where 字句的表达式或者函数里面已经包含某个列,
则不会使用该列上面的索引. 为了方便此类操作oracle 提供了一个选项,可以基于一个或者多个列上的函数
或者表达式上面创建索引.

select * from app_accountpro t where t.acc_name='' --0.125秒
select * from app_accountpro t where upper(t.acc_name)=''---- 29.4秒
在此函数上建立索引
create index index_accname on app_accountpro(upper(acc_name));
再次查询
select * from app_accountpro t where upper(t.acc_name)=''---- 0.141秒

在自定义函数上建立索引
create or replace function CONVERT_ID_15 (/*转换身份证号为15位*/
  p_id2 in varchar2
) return varchar2
is
  p_id varchar2(20);
  id varchar2(15);
begin
  p_id:=ltrim(p_id2);
  p_id:=rtrim(p_id);

  if length(p_id) = 18 then
    id:=substr(p_id, 1, 6) || substr(p_id, 9, 9);
  else
    id:=substr(p_id, 1, 15);
  end if;

  return id;
exception
  when others then
    return null;
end
;

create index index_hf_id on app_accountpro(CONVERT_ID_15(hf_id))
报错ORA-30553: 函数不能确定（ORA-30553: The function is not deterministic）
修改视图
create or replace function CONVERT_ID_15 (/*转换身份证号为15位*/
  p_id2 in varchar2
) return varchar2 deterministic
is
  p_id varchar2(20);
  id varchar2(15);
begin
  p_id:=ltrim(p_id2);
  p_id:=rtrim(p_id);

  if length(p_id) = 18 then
    id:=substr(p_id, 1, 6) || substr(p_id, 9, 9);
  else
    id:=substr(p_id, 1, 15);
  end if;

  return id;
exception
  when others then
    return null;
end
;
创建索引成功.
未创建索引之前
select t.hf_id from app_accountpro t where CONVERT_ID_15(t.hf_id)='430221870924382';--59.412秒
创建索引之后 0.078秒.

另外索引还可以基于表达式
create index on app_tab1(num1*num2);