建立函数索引须知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));

索引已创建。