基于自定义函数的Function-Based索引创建

最新推荐文章于 2021-04-16 18:00:00 发布

xinxiangsui2008

最新推荐文章于 2021-04-16 18:00:00 发布

阅读量2k

点赞数

分类专栏： SQL Tuning

SQL Tuning 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

随便一个测试可以再现这个问题,我门创建一个函数(本范例函数用于进行16进制向10进制转换):

CREATE OR REPLACE FUNCTION h2ten (
   p_str         IN   VARCHAR2,
   p_from_base   IN   NUMBER DEFAULT 16
)
   RETURN NUMBER 
IS
   l_num   number
DEFAULT 0;   l_hex   VARCHAR2 (16) DEFAULT '0123456789ABCDEF';BEGIN   FOR i IN 1 .. LENGTH (p_str)   LOOP      l_num :=         l_num * p_from_base + INSTR (l_hex, UPPER (SUBSTR (p_str, i, 1)))         - 1;   END LOOP;

   RETURN l_num;
END h2ten;

此时创建索引,获得如下错误信息:

SQL> create table t as select username,'a' hex from dba_users;

Table created

SQL> create index i_t on t (h2ten(hex));

create index i_t on t (h2ten(hex))

ORA-30553: The function is not deterministic

如果需要创建基于自定义函数的索引,那么我们需要指定deterministic参数:

CREATE OR REPLACE FUNCTION h2ten (
   p_str         IN   VARCHAR2,
   p_from_base   IN   NUMBER DEFAULT 16
)
   RETURN NUMBER DETERMINISTIC
IS
   l_num   NUMBER        DEFAULT 0;
   l_hex   VARCHAR2 (16) DEFAULT '0123456789ABCDEF';
BEGIN
   FOR i IN 1 .. LENGTH (p_str)
   LOOP
      l_num :=
         l_num * p_from_base + INSTR (l_hex, UPPER (SUBSTR (p_str, i, 1)))
         - 1;
   END LOOP;

   RETURN l_num;
END h2ten;

此时创建索引即可:

SQL> create index i_t on t (h2ten(hex));

Index created

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 DETERMINISTICfunctions can be called from a function-based index or a materialized view that has query-rewrite enabled.

FROM:http://www.eygle.com/archives/2006/01/create_function_based_index.html