基于自定義函數(shù)的Function-Based索引創(chuàng)建

2024-07-21 02:32:48

字體：大中小

供稿：網(wǎng)友

留言版上的第2330號(hào)問題是:

在oralce中給自建函數(shù)創(chuàng)建索引，結(jié)果不成功。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.

我們看一下這是為什么?隨便一個(gè)測(cè)試可以再現(xiàn)這個(gè)問題,我門創(chuàng)建一個(gè)函數(shù)(本范例函數(shù)用于進(jìn)行16進(jìn)制向10進(jìn)制轉(zhuǎn)換):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;
此時(shí)創(chuàng)建索引,獲得如下錯(cuò)誤信息:SQL> create table t as select username,'a' hex from dba_users;Table createdSQL> create index i_t on t (h2ten(hex));create index i_t on t (h2ten(hex))ORA-30553: The function is not deterministic
假如需要?jiǎng)?chuàng)建基于自定義函數(shù)的索引,那么我們需要指定deterministic參數(shù):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;此時(shí)創(chuàng)建索引即可: SQL> create index i_t on t (h2ten(hex));Index created Oracle這樣解釋這個(gè)參數(shù):

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.

上一篇：冷備份能應(yīng)用歸檔日志么？

下一篇：使用Index提示強(qiáng)制使用索引