關於函式索引(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的才可以。