自定义函数上的函数索引

当用户在FBI中使用自定义函数时须加关键字:deterministic,就是说必须使用deterministic function……

 

ORACLE8>create table test as select * from all_objects;
 
Table created.

ORACLE8>create or replace function f_lower(name varchar2) return varchar2 as
 begin
 return lower(name);
 end;
/  2    3    4    5
 
Function created.

ORACLE8>create index test_idx on test(substr(f_lower(object_name),1,30));
create index test_idx on test(substr(f_lower(object_name),1,30))
                                     *
ERROR at line 1:
ORA-30553: The function is not deterministic

ORACLE8>create or replace function f_lower(name varchar2)
return varchar2 DETERMINISTIC
as
 begin
 return lower(name);
 end;
/
  2    3    4    5    6    7
Function created.
 
ORACLE8>create index test_idx on test(substr(f_lower(object_name),1,30));
 
Index created.