在oralce中给自建函数创建索引,结果不成功。
source:Create Index IDX_T_SP_TWOTYPESTAT_0_f On T_SP_TWOTYPESTAT_0(f_dateadd(yearmonth,12,2));
err:the function is not deterministic.
我们看一下这是为什么?
随便一个测试可以再现这个问题,我门创建一个函数(本范例函数用于进行16进制向10进制转换):
CREATE OR REPLACE FUNCTION h2ten ( RETURN l_num; |
此时创建索引,获得如下错误信息:
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 ( RETURN l_num; |
此时创建索引即可:
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. OnlyDETERMINISTIC
functions can be called from a function-based index or a materialized view that has query-rewrite enabled.
总机:(010)-88589926,88589826,88587026 QQ讨论群:243729577 182441349 邮箱:cuug_bj@cuug.com
通信地址:北京市海淀区紫竹院路98号北京化工大学科技园609室(CUUG)邮政编码:100089
中国UNIX用户协会 Copyright 2010 ALL Rights Reserved 北京神脑资讯技术有限公司
京ICP备11008061号 京公网安备110108006275号