建立函式索引須知DETERMINISTIC

比如我們先寫一個自定義的函式，此函式是將16進位制轉換為10進位制

SQL> CREATE OR REPLACE FUNCTION hex2ten
  2  (
  3      p_str         IN   VARCHAR2,
  4      p_from_base   IN   NUMBER DEFAULT 16
  5  )   RETURN NUMBER
  6  IS
  7      l_num   NUMBER        DEFAULT 0;
  8      l_hex   VARCHAR2 (16) DEFAULT '0123456789ABCDEF';
  9  BEGIN
 10      FOR i IN 1 .. LENGTH (p_str)
 11      LOOP
 12         l_num :=  l_num * p_from_base + INSTR (l_hex, UPPER (SUBSTR (p_str, i, 1))) - 1;
 13      END LOOP;
 14
 15      RETURN l_num;
 16  END hex2ten;
 17  /

函式已建立。

我們建一個表做個測試

SQL> create table alan
  2  as
  3  select username, 'b' HEX from dba_users;

表已建立。

之後為這個表建立一個基於自定義的函式索引

SQL> create index alan_ind_fun on alan(hex2ten(HEX));
create index alan_ind_fun on alan(hex2ten(HEX))
                                  *
ERROR 位於第 1 行:
ORA-30553: 函式不能確定

這是為什麼呢？

這是因為我們在建立函式的時候沒有加一個hint,oracle是這樣解釋的：
The hint DETERMINISTIC helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled.

因此在建立function的時候我們可以加一個hint

SQL> drop function hex2ten
  2  ;

函式已丟棄。

SQL> CREATE OR REPLACE FUNCTION hex2ten
  2  (
  3      p_str         IN   VARCHAR2,
  4      p_from_base   IN   NUMBER DEFAULT 16
  5  )   RETURN NUMBER   DETERMINISTIC
  6  IS
  7      l_num   NUMBER        DEFAULT 0;
  8      l_hex   VARCHAR2 (16) DEFAULT '0123456789ABCDEF';
  9  BEGIN
 10      FOR i IN 1 .. LENGTH (p_str)
 11      LOOP
 12         l_num :=  l_num * p_from_base + INSTR (l_hex, UPPER (SUBSTR (p_str, i, 1))) - 1;
 13      END LOOP;
 14
 15      RETURN l_num;
 16  END hex2ten;
 17  /

函式已建立。

這樣就可以成功的建立索引了。

SQL> create index alan_ind_fun on alan(hex2ten(HEX));

索引已建立。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-1291/，如需轉載，請註明出處，否則將追究法律責任。