关于函数索引(function-based index)

函数索引是从8i开始提供的，有了函数索引就可以在索引中使用函数或者表达式了。

例：

SQL> create table sunwg (id varchar2(10));

Table created.

[@more@]

SQL> insert into sunwg values('a');

1 row created.

SQL> commit;

Commit complete.

SQL>create index ind_sunwg on sunwg(id);

SQL>select * from sunwg where upper(id) = ‘TOM’;

这样的情况下，这个SQL是不会走索引的，因为在ID上面存在函数UPPER，所以只能走全表扫描。

但是利用函数索引，上面的SQL也是可以走索引的。

SQL>drop index ind_sunwg;

SQL>create index ind_sunwg on sunwg(upper(id));

SQL>analyze table sunwg compute statistics for table for all indexes for all indexed columns;

打开AUTOTRACE重新执行上面的SQL，可以发现已经可以走索引了。

上面的函数是系统自带的，那么使用自己定义的函数呢？

SQL>create or replace function md5(n varchar2)

2 return varchar2

3 as

4 n_string varchar2(100) default null;

5 begin

6 dbms_obfuscation_toolkit.md5(INPUT_STRING => n,CHECKSUM_STRING => n_string);

7 return RAWTOHEX(UTL_RAW.CAST_TO_raw(n_string));

8 end;

/

SQL>drop index ind_sunwg;

SQL> create index ind_sunwg on sunwg(md5(id));

create index ind_sunwg on sunwg(md5(id))

*

ERROR at line 1:

ORA-30553: The function is not deterministic

可以看出来用自己定义的函数来建函数索引会报一个ORA-30553的错误。错误的原因是因为函数没有声明确定性，修改函数增加确定性标识deterministic。

SQL>create or replace function md5(n varchar2)

2 return varchar2 deterministic

3 as

4 n_string varchar2(100) default null;

5 begin

6 dbms_obfuscation_toolkit.md5(INPUT_STRING => n,CHECKSUM_STRING => n_string);

7 return RAWTOHEX(UTL_RAW.CAST_TO_raw(n_string));

8 end;

/

SQL> create index ind_sunwg on sunwg(md5(id));

Index created.

增加确定性标识后就没有上面的问题了。

下面这些是网友总结的关于函数索引的限制和规则对于下面这些限制，不能创建FBI索引：
a) LOB 列
b) REF
c) Nested table 列
d) 包含上面数据类型的对象

FBI索引必须遵守下面的规则：

a) 必须使用基于成本的优化器，而且创建后必须对索引进行分析
b) 不能存储NULL值。因为任何函数在任何情况下都不能返回NULL值。
c)如果一个用户定义的PL/SQL例程失效了，而且这个例程被FBI索引用到了，那么相应的这个FBI索引会变成DISABLED
d)创建FBI索引得函数必须是确定性的。即，对于指定的输入，总是会返回确定的结果。
e) 索引的属主如果没有了在FBI索引里面使用的函数的执行权限，那么这个FBI索引会变成DISABLED.
f) 在创建索引得函数里面不能使用SUM等总计函数。
g)要把一个DISABLED了的索引重新变成ENABLED，这个函数必须首先是ENABLED的才可以。

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

转载于:http://blog.itpub.net/8394333/viewspace-996327/