警惕！自定义函数索引的那些陷阱及避坑术

作者介绍

丁俊，DBAplus社群联合发起人，新炬网络专家团成员，性能优化专家，Oracle ACEA，ITPUB开发版资深版主。十年电信行业从业经验，从事过系统开发与维护、业务架构和数据分析、系统优化等工作。电子工业出版社终身荣誉作者，《剑破冰山-Oracle开发艺术》副主编。

当我们对列使用了函数运算之后，如果此列没有函数索引，那么普通索引是无效的。比如where substr(name,1,3)='abc';如果建立了create INDEX idx_t ON t(NAME);

那么谓词是无法使用此索引做范围扫描的。在Oracle中允许定义函数索引（FUNCTION BASED INDEX,简称FBI)，函数索引可以是基于内置函数的，也可以是自定义函数的，本文主要讲述基于自定义函数的索引用法及其注意点。

当需要对列进行复杂的运算，复杂的规则需要自定义函数的时候，如果需要走索引，那么必须建立自定义函数的索引。建立自定义函数索引有几点要注意：

1、自定义函数必须加DETERMINISTIC关键字，让Oracle知道此函数对于每个入参的返回结果都是确定的唯一的。

道理很明显，如果一样的入参，结果不同，那么查询的结果必然有问题，必须要用这个关键字告诉Oracle，此函数索引是可以信任的。但是有个问题得注意：因为自定义函数是一系列逻辑规则，就算定义的函数对每个入参返回的值不唯一（比如用了SYSDATE，RANDOM等运算），但是使用了DETERMINISTIC关键字，让Oracle相信唯一，但是实际情况不唯一，那么使用函数索引查询的结果必然也是有问题的。所以使用函数索引要注意：必须从逻辑上确定对于一样的入参返回的结果是一样的，因为Oracle不会检查你的逻辑。

2、一旦改变函数定义，必须REBUILD对应的函数索引

很显然，函数索引中存储的是表中的列或表达式作为自定义函数的参数的运算结果，如果函数改变，Oracle不会自动rebulid函数索引对应的值，这样如果继续使用函数索引，必然结果可能出错。

下面分别对上面的内容举例说明：

针对第1点的例子：

Of course，现在的结果是没有问题的，但是本身这个自定义函数中的TO_DATE(param,'yyyy')针对不同月份的插入结果返回的都是当月的第一天，如果我是6月插入:

现在是查询：

上面的结果是令人迷惑的，因为表里存储的有2行2013，但是最终结果却只查询出一行。究其原因，就是自定义函数虽然使用了DETERMINISTIC关键字，但是Oracle只管有没有这关键字，而不会管你的函数逻辑是否真的对每个相同的输入，有一样的输出，这里我们使用DETERMINISTIC关键字，欺骗了Oracle。很显然，虽然在表里存储的2行都是2013，但是一个5月份插入的，一个6月份插入的，通过函数运算，一个索引中存储的是2013-5-1，一个是2013-6-1，所以使用2013-5-1里查询的时候，只返回1行。如果自定义中有类似于DBMS_RANDOM，SYS_GUID等不确定或随时间变化值不同的，那么也会产生此混乱结果。

另外很多书上说函数索引必须：

Oracle使用函数索引，会进行查询重写，要求下面两个参数开启：
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED

经过测试，发现在本环境11g下无影响。

针对第2点的例子：

函数索引的函数定义不能随便改变，改变就必须rebuild函数索引(or删除重建），因为函数索引中会存储对应函数运算的结果，然后在使用函数索引访问的时候，不用再调用函数，so,函数改变，Oracle不会级联rebuild其函数索引，所以，改变函数逻辑不手动rebuild，必然是危险的。

走全表扫描，函数会对每行都调用1次。