函数索引的使用细节——自定义函数的索引化.doc

函数索引的使用细节——自定义函数的索引化 函数索引使用细节——自定义函数的索引化 个人分类：Oracle管理 在笔者之前的Blog中，已经多次对函数索引Functional Index进行介绍。其中包括函数索引的原理、使用和应用场景。针对两个使用细节，补充一下其他知识内容。 函数索引可以解决一些由于SQL书写和数据库设计带来的优化问题。我们通常使用的也是一些Oracle预定义的函数功能，那么我们是否可以对自定义函数也加函数索引呢？ 首先还是对环境的准备： SQL desc t Name Type Nullable Default Comments -------------- ------------- -------- ------- -------- OWNER VARCHAR2(30) Y OBJECT_NAME VARCHAR2(128) Y SUBOBJECT_NAME VARCHAR2(30) Y OBJECT_ID NUMBER Y （篇幅原因，有省略…） 此时，我们建立一个简单的自定义函数f_define_nc。 SQL create or replace function f_define_nc 2 (inpstr in varchar2) 3 return varchar2 is 4 Result varchar2(10); 5 begin 6 Result := substr(inpstr,1,5); 7 return(Result); 8 end f_define_nc; 9 / Function created 对数据表T的列object_name加函数索引处理。 SQL create index idx_t_fobjname on t(f_define_nc(object_name)); create index idx_t_fobjname on t(f_define_nc(object_name)) ORA-30553:函数不能确定 检查30553错误信息。 [oracle@bspdev ~]$ oerr ora 30553 30553, 00000, The function is not deterministic // *Cause: The function on which the index is defined is not deterministic // *Action: If the function is deterministic, mark it DETERMINISTIC. If it // is not deterministic (it depends on package state, database state, // current time, or anything other than the function inputs) then // do not create the index. The values returned by a deterministic // function should not change even when the function is rewritten or // recompiled. 简单的说，Oracle不允许对所谓“非确定性的函数”建立函数索引。那么，什么是非确定性函数呢？ 一个函数，无论有无参数，对相同的输入函数都能获得相同的输出，我们称之为确定性“Deterministic”函数。否则是非确定性函数。例如substr函数，就是一个确定性函数。sysdate就是一个非确定性函数。 要对一个自定义函数建立函数索引，要保证该函数是一个确定性函数，就需要使用deterministic进行标注才行。 SQL create or replace function f_define_nc 2 (inpstr in varchar2) 3 return varchar2 4 DETERMINISTIC 5 is 6 Result varchar2(10); 7 begin 8 Result := substr(inps