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

« 年终难终进入数据库事故多发期 | Blog首页 | 看图说话需要学习法语么 »

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

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2006/01/create_function_based_index.html

站内相关文章|Related Articles

索引与Null值对于Hints及执行计划的影响

使用Index提示强制使用索引

工作日志：10 亿记录表的几个索引创建时间

工作记录: 2亿记录的导入及索引创建

CBO的魔术 - 一个错误的索引选择会带来的后果

留言版上的第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.

我们看一下这是为什么?

随便一个测试可以再现这个问题,我门创建一个函数(本范例函数用于进行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 DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled.

历史上的今天...
>> 2009-01-23文章:

红梅枝头春意闹

新年快乐 - 祝愿前程似锦、花好人团圆

>> 2007-01-23文章:

AskTom网站2006年内容CHM版本下载

By eygle on 2006-01-23 10:59 | Comments (2) | Posted to SQL.PLSQL | Edit |

相关文章	随机文章
索引与Null值对于Hints及执行计划的影响使用Index提示强制使用索引工作日志：10 亿记录表的几个索引创建时间工作记录: 2亿记录的导入及索引创建 CBO的魔术 - 一个错误的索引选择会带来的后果	新年有好运 - 2009中国邮政有奖明信片中奖 Oracle HowTo:How to get Oracle SCN? 进京一周年记-eygle在北京的生活之二 Tom's New book has landed 为何而心跳-Oracle Heartbeat研究之二

搜索本站:

留言 (2)

9i后不再需要query rewrite了

Posted by: anysql at January 24, 2006 8:13 AM

这里是指QUERY REWRITE Clause 而不是初始化参数.

Posted by: eygle at January 24, 2006 9:36 AM

www.eygle.com

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

留言 (2)

发表留言: