函式索引使用細節——自定義函式的索引化

 

在筆者之前的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就認為函式的定義者會去保證函式自身的確定性，就不去進行其他驗證。一旦建立函式索引，進行查詢的時候也會使用到。