基于自定义函数的Function

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

发表日期：2008-2-9

新浪微博QQ空间QQ微博百度搜藏腾讯朋友QQ收藏百度空间人人网开心网

-

留言版上的第2330号问题是:

在oralce中给自建函数创建索引，结果不成功。source:Create Index IDX_T_SP_TWOTYPESTA T_0_f On T_SP_TWOTYPESTA T_0(f_dateadd(yearmonth,12,2)); err:the function is not deterministic.

我们看一下这是为什么? 随便一个测试可以再现这个问题,我门创建一个函数(本范例函数用于进行16进制向10进制转换): CREA TE OR REPLACE FUNCTION h2ten ( p_str IN V ARCHAR2,

p_from_base IN NUMBER DEFAULT 16

)

RETURN NUMBER

IS

l_num NUMBER DEFAULT 0;

l_hex V ARCHAR2 (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 V ARCHAR2,

p_from_base IN NUMBER DEFAULT 16

)

RETURN NUMBER DETERMINISTIC

IS

l_num NUMBER DEFAULT 0;

l_hex V ARCHAR2 (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 DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled.

-

关注此文的读者还看过：

·2011-12-5 15:19:56 Oracle中细粒度访问控制的工作方式

·2011-12-5 15:19:56 索引与Null值对于Hints及执行计划的影响

·2011-12-5 15:19:51 建立与Oracle服务器连接的两种连接模式

·2011-12-5 15:19:50 展示JDBC存取ORACLE大型数据对象LOB几种情况的示范类·2011-12-5 15:19:48 重建密码文件--解决ORA-01991错误

·2011-12-5 15:19:48 Oracle中对两个数据表交集查询简介

·2011-12-5 15:19:46 Oracle10g 控制文件的改变

·2011-12-5 15:19:44 Oracle系统密码文件创建、使用及维护

·2011-12-5 15:19:42 Oracle在Linux操作系统下安装小结