自定义函数索引DETERMINISTIC减少函数调用次数

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 行。