基于自定義函數的

留言版上的第2330號問題是:

在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.

我們看一下這是為什么?

單注獎金為28.59萬。當期我省達州彩民贏得幸運 隨便一個測試可以再現這個問題,我門創建一個函數(本范例函數用于進行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 \'ABCDEF\';

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 \'ABCDEF\';

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.

查看本文來源