  當我們對列使用了函式運算之後，如果此列沒有函式索引，那麼普通索引是無效的。比如where substr(name,1,3)=’abc’;如果建立了create INDEX idx_t ON t(NAME);
   那麼謂詞是無法使用此索引做範圍掃描的。在oracle中允許定義函式索引（FUNCTION BASED INDEX,簡稱FBI)，函式索引可以是基於內建函式的，也可以是自定義函式的，
   本文主要講述基於自定義函式的索引用法及其注意點。

    當需要對列進行復雜的運算，複雜的規則需要自定義函式的時候，如果需要走索引，那麼必須建立自定義函式的索引。建立自定義函式索引有幾點要注意：
    1.自定義函式必須加DETERMINISTIC關鍵字，讓ORACLE知道此函式對於每個入參的返回結果都是確定的唯一的。
   道理很明顯，如果一樣的入參，結果不同，那麼查詢的結果必然有問題，必須要用這個關鍵字告訴ORACLE，此函式索引是可以信任的。但是有個問題得注意：因為自定義函式是一系列邏輯規則，就算定義的函式對每個入參返回的值不唯一（比如用了SYSDATE，RANDOM等運算），但是使用了DETERMINISTIC關鍵字，讓ORACLE相信唯一，事實不唯一，那麼使用函式索引查詢的結果必然也是有問題的。所以使用函式索引要注意：必須從邏輯上確定對於一樣的入參返回的結果是一樣的，因為ORACLE不會檢查你的邏輯。

  2.一旦改變函式定義，必須REBUILD對應的函式索引
    很顯然，函式索引中儲存的是表中的列或表示式作為自定義函式的引數的運算結果，如果函式改變，ORACLE不會自動REBUILD函式索引對應的值，這樣如果繼續使用函式索引，必然結果可能出錯。

下面分別對上面的內容舉例說明：
針對第1點的例子：

–使用自定義函式索引，必須加DETERMINISTIC，並且實際對應一樣的輸入引數，返回的結果就是一樣的，否則會導致錯誤
dingjun123@ORADB> CREATE OR REPLACE FUNCTION get_date(param_in VARCHAR2)
  2 RETURN DATE DETERMINISTIC
  3 AS
  4 BEGIN
  5   RETURN TO_DATE(param_in,’yyyy’);
  6 END;
  7 /

Function created.

dingjun123@ORADB> DROP TABLE t;
Table dropped.

dingjun123@ORADB> CREATE TABLE t(a VARCHAR2(10));
Table created.

dingjun123@ORADB> CREATE INDEX idx_t ON t(get_date(a));
Index created.

–2013-年5月份插入
dingjun123@ORADB> INSERT INTO t VALUES(’2013′);
1 row created.

dingjun123@ORADB> commit;
Commit complete.

dingjun123@ORADB> SELECT * FROM t WHERE get_date(a)=DATE’2013-5-1′;
A
———-
2013
1 row selected.

OF COURSE,現在的結果是沒有問題的，但是本身這個自定義函式中的TO_DATE(param,’yyyy’)針對不同月份的插入結果返回的都是當月的第一天，如果我是6月插入:

–2013年6月份插入
dingjun123@ORADB> INSERT INTO t VALUES(’2013′);
1 row created.

dingjun123@ORADB> COMMIT;
Commit complete.

dingjun123@ORADB> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
Session altered.

dingjun123@ORADB> select a from t;
A
———-
2013
2013
2 rows selected.

現在是查詢：

dingjun123@ORADB> SELECT * FROM t WHERE get_date(a)=DATE’2013-5-1′;
A
———-
2013

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 1594971208
————————————————————————————-
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T     |     1 |    16 |     1   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
2 – access(“DINGJUN123″.”GET_DATE”(“A”)=TO_DATE(‘ 2013-05-01 00:00:00′,
‘syyyy-mm-dd hh24:mi:ss’))

上面的結果是令人迷惑的，因為表裡儲存的有2行2013，但是最終結果卻只查詢出一行。究其原因，就是自定義函式雖然使用了DETERMINISTIC關鍵字，但是ORACLE只管有沒有這關鍵字，而不會管你的函式邏輯是否真的對每個相同的輸入，有一樣的輸出，這裡我們使用DETERMINISTIC關鍵字，欺騙了ORACLE。很顯然，雖然在表裡儲存的2行都是2013，但是一個5月份插入的，一個6月份插入的，透過函式運算，一個索引中儲存的是2013-5-1，一個是2013-6-1，所以使用2013-5-1裡查詢的時候，只返回1行。如果自定義中有類似於DBMS_RANDOM，SYS_GUID等不確定或隨時間變化值不同的，那麼也會產生此混亂結果。

  另外很多書上說函式索引必須：
   ORACLE使用函式索引，會進行查詢重寫，要求下面兩個引數開啟：
   QUERY_REWRITE_ENABLED=TRUE
   QUERY_REWRITE_INTEGRITY=TRUSTED
   經過測試，發現在本環境11g下無影響，後來看了yangtingkun大師的文章，原來早就沒有影響了。http://space.itpub.net/4227/viewspace-68620

針對第2點的例子：
函式索引的函式定義不能隨便改變，改變就必須rebuild函式索引(or刪除重建），因為函式索引中會儲存對應函式運算的結果，然後在使用函式索引訪問的時候，不用再呼叫函式，so,函式改變，oracle不會級聯rebuild其函式索引，所以，改變函式邏輯不手動rebuild，必然是危險的。

走全表掃描，函式會對每行都呼叫1次(當然DETERMINSTIC函式是可以有快取效果的，以後再說明）：

dingjun123@ORADB> DROP TABLE tt;
Table dropped.

dingjun123@ORADB> CREATE TABLE tt(NAME VARCHAR2(10));
Table created.

dingjun123@ORADB> INSERT INTO tt
2 SELECT LEVEL FROM dual CONNECT BY LEVEL < 1000;
999 rows created.

–DBMS_APPLICATION_INFO包監控函式的呼叫次數
dingjun123@ORADB> CREATE OR REPLACE FUNCTION func_tt(x IN VARCHAR2)
  2 RETURN VARCHAR2 DETERMINISTIC AS
  3 BEGIN
  4      DBMS_APPLICATION_INFO.set_client_info(USERENV(‘client_info’)+1 );
  5      RETURN ‘o’ || x;
  6 END;
  7 /
Function created.

dingjun123@ORADB> EXEC DBMS_APPLICATION_INFO.set_client_info(0);
PL/SQL procedure successfully completed.

dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) <= ‘mmmmm6′;
no rows selected

dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

無函式索引，全表掃描，訪問對每行都呼叫函式，一條SQL訪問函式999次。如果使用函式索引，那麼必然在建立（DML）的時候，會自動呼叫函式，索引中儲存對應的key與函式運算結果值，所以，再使用到函式索引的時候，不用再呼叫函式，而且索引訪問還提高效率，達到多種提高效率的效果。

–重置計數器
dingjun123@ORADB> EXEC DBMS_APPLICATION_INFO.set_client_info(0);
PL/SQL procedure successfully completed.

dingjun123@ORADB> CREATE INDEX idx_tt ON tt(func_tt(NAME));
Index created.

–建立索引的時候就呼叫函式了
dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

dingjun123@ORADB> set autotrace traceonly

–使用的時候不再呼叫函式，因為已經呼叫過函式，函式運算的結果已經儲存到索引中了
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
1 row selected.

Execution Plan
———————————————————-
Plan hash value: 6977672
————————————————————————————–
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT            |        |    10 | 20090 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TT     |    10 | 20090 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_TT |     4 |       |     1   (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
2 – access(“DINGJUN123″.”FUNC_TT”(“NAME”)=’o1′)
Note
—–
- dynamic sampling used for this statement (level=2)

Statistics
———————————————————-
         24 recursive calls
          0 db block gets
         14 consistent gets
          0 physical reads
          0 redo size
        417 bytes sent via SQL*Net to client
        416 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

使用自定義函式索引是危險的，如果修改函式定義，沒有rebuild或刪除重建函式索引，那麼函式索引中儲存的還是舊的函式運算結果，這樣會導致錯誤：

dingjun123@ORADB> CREATE OR REPLACE FUNCTION func_tt(x IN VARCHAR2)
  2 RETURN VARCHAR2 DETERMINISTIC AS
  3 BEGIN
  4      DBMS_APPLICATION_INFO.set_client_info(USERENV(‘client_info’)+1 );
  5      RETURN ‘a’ || x;
  6 END;
  7 /
Function created.

–查詢不對，函式應該運算結果’o1′應該沒有行，但是因為索引沒有被rebuild
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
NAME
———-
1
1 row selected.

–強制全表掃描，正確
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;

no rows selected

–rebuild索引後也正確
dingjun123@ORADB> alter index idx_tt rebuild;
Index altered.

dingjun123@ORADB> set autotrace traceonly
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
no rows selected

Execution Plan
———————————————————-
Plan hash value: 6977672
————————————————————————————–
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT            |        |    10 | 20090 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TT     |    10 | 20090 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_TT |     4 |       |     1   (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
2 – access(“DINGJUN123″.”FUNC_TT”(“NAME”)=’o1′)

在不得不使用函式索引來提高效率的時候，別忘記了，隨時準備維護函式索引，而且別弄出奇奇怪怪的函式索引，導致亂七八糟的問題，那樣就不好了！

自定義函式索引使用及其注意點

相關文章