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

 

在笔者之前的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(inpstr,1,5);

  9    return(Result);

 10  end f_define_nc;

 11  /

 

Function created

 

 

此时，我们再次建立函数索引。

 

//建立函数索引

SQL> create index idx_t_fobjname on t(f_define_nc(object_name));

Index created

 

//收集统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

//生成执行计划

SQL> explain plan for select * from t where f_define_nc(object_name)='I_IND';

Explained.

 

SQL> select * from table(dbms_xplan.display(format=>'advanced'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------

Plan hash value: 669064648

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     4 |   412 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     4 |   412 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_FOBJNAME |     4 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

 

 

这样，我们成功的建立了函数索引。

 

下面，有两个问题需要进行思考：

 

其一，为什么Oracle要求函数是一个确定性的函数，才能建立函数索引。这个问题需要从函数索引的原理入手。函数索引是一种普通索引变种，Oracle在数据表中添加一个冗余列，将函数处理的结果保存在这个冗余列中。之后在对这个冗余列进行普通索引的建立。函数处理值都是保存在索引的叶子节点中。

 

如果函数是一个不确定性的函数，这样保存在冗余列和索引叶子节点上的检索键也就失去了意义。函数索引检索结果就是错误的。

 

其二，就是函数确定性声明方式。只通过一个deterministic关键字进行声明，就可以建立。这样真的没有问题吗？我们进行下列实验：

 

//定义一个典型的不确定性函数；

SQL> create or replace function f_test_nc(i_vc_word in varchar2)

  2  return number

  3  DETERMINISTIC

  4  is

  5    Result number;

  6  begin

  7    result := length(i_vc_word)+100*dbms_random.value;

  8    return(Result);

  9  end f_test_nc;

 10  /

 

Function created

 

 

使用dbms_random函数，必然结果是不确定性的。

 

 

SQL> select f_test_nc('df') from dual;

 

F_TEST_NC('DF')

--------------------------------------------------------------------------------

15.648738643901598708753466833273910001

 

SQL> select f_test_nc('df') from dual;

 

F_TEST_NC('DF')

--------------------------------------------------------------------------------

52.17432021982655792498049873516166188

 

SQL> select f_test_nc('df') from dual;

 

F_TEST_NC('DF')

--------------------------------------------------------------------------------

30.245275513714721819304937071210704424

 

 

添加在函数索引中呢？

 

//成功建立索引

SQL> create index idx_t_supname on t(f_test_nc(object_name));

Index created

 

 

SQL> explain plan for select * from t where f_test_nc(object_name)=10;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1966470384

--------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    98 |     2   (0

|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |    98 |     2   (0

|*  2 |   INDEX RANGE SCAN          | IDX_T_SUPNAME |     1 |       |     1   (0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("SYS"."F_TEST_NC"("OBJECT_NAME")=10)

14 rows selected

 

 

可见，只要声明了deterministic关键字在函数中。Oracle就认为函数的定义者会去保证函数自身的确定性，就不去进行其他验证。一旦建立函数索引，进行查询的时候也会使用到。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-698041/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-698041/