自定义函数索引deterministic减少函数调用次数-凯发app官方网站

1）deterministic不建立索引，缓存无效，执行的时候还是调用函数，
--自定义函数，要建立索引，必须有deterministic关键字，否则不确定不可以
dingjun123@oradb> create or replace function func_tt(x in varchar2)
  2      return varchar2 deterministic
  3    as
  4     begin
  5       dbms_application_info.set_client_info(userenv('client_info') 1 );
  6     return 'a'||x;
  7    end;
  8   /


函数已创建。


已用时间:  00: 00: 00.26
dingjun123@oradb> drop table t;


表已删除。


已用时间:  00: 00: 01.62
dingjun123@oradb>  create table t(m varchar2(100) not null);


表已创建。


已用时间:  00: 00: 00.37
dingjun123@oradb>  insert into t select level from dual connect by level<1000;


已创建999行。


已用时间:  00: 00: 00.04
dingjun123@oradb> commit;


提交完成。


已用时间:  00: 00: 00.04
dingjun123@oradb> exec dbms_application_info.set_client_info(0);


pl/sql 过程已成功完成。


已用时间:  00: 00: 00.00
dingjun123@oradb> select userenv('client_info') from dual;


userenv('client_info')
----------------------------------------------------------------
0


已选择 1 行。


已用时间:  00: 00: 00.01
dingjun123@oradb> select * from t where func_tt(m)='a2';


m
----------------------------------------------------------------------------------------------------
2


已选择 1 行。


已用时间:  00: 00: 00.04
dingjun123@oradb> select userenv('client_info') from dual;


userenv('client_info')
----------------------------------------------------------------
999


已选择 1 行。


已用时间:  00: 00: 00.01
dingjun123@oradb> select * from t where func_tt(m)='a2';


m
----------------------------------------------------------------------------------------------------
2


已选择 1 行。


已用时间:  00: 00: 00.03
dingjun123@oradb> select userenv('client_info') from dual;


userenv('client_info')
----------------------------------------------------------------
1998


已选择 1 行。


已用时间:  00: 00: 00.00




2.deterministic 索引才能缓存，执行的时候不再调用函数
dingjun123>create or replace function func_tt(x in varchar2)
  2      return varchar2 deterministic
  3      as
  4      begin
  5      dbms_application_info.set_client_info(userenv('client_info') 1 );
  6       return 'a'||x;
  7      end;
  8  /


函数已创建。


dingjun123> drop table t;


表已删除。


dingjun123> create table t(m varchar2(100) not null);


表已创建。


dingjun123> insert into t select level from dual connect by level<1000;


已创建999行。


dingjun123> commit;


提交完成。


dingjun123>exec dbms_application_info.set_client_info(0);


pl/sql 过程已成功完成。


dingjun123>create index idx_t on t (func_tt(m));


索引已创建。


dingjun123> exec    dbms_stats.gather_table_stats(user,'t',cascade => true);


pl/sql 过程已成功完成。


--这里创建索引，函数就被调用很多次了




dingjun123> select userenv('client_info') from dual;


userenv('client_info')
---------------------------------------------------------------------------------------------------------
5994


已选择 1 行。




dingjun123> select * from t where func_tt(m)='a2';


m
---------------------------------------------------------------------------------------------------------
2


已选择 1 行。


--不需要再调用函数


dingjun123> select userenv('client_info') from dual;


userenv('client_info')
---------------------------------------------------------------------------------------------------------
5994


已选择 1 行。