自定义函数索引使用及其注意点

???当我们对列使用了函数运算之后，如果此列没有函数索引，那么普通索引是无效的。比如where substr(name,1,3)=abc;如果建立了create INDEX idx_t ON t(NAME); ? 那么谓词是无法使用此索引做范围扫描的。在oracle中允许定义函数索引（FUNCTION BASED INDEX,

???当我们对列使用了函数运算之后，如果此列没有函数索引，那么普通索引是无效的。比如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不会自动REBUILD函数索引对应的值，这样如果继续使用函数 ? ? ? ? 索引，必然结果可能出错。

? ? ?下面分别对上面的内容举例说明：
针对第1点的例子：

–使用自定义函数索引，必须加DETERMINISTIC，并且实际对应一样的输入参数，返回的结果就是一样的，否则会导致错误
dingjun123@ORADB> CREATE OR REPLACE FUNCTION get_date(param_in VARCHAR2)
? 2 ?RETURN DATE DETERMINISTIC
? 3 ?AS
? 4 ?BEGIN
? 5 ? RETURN?TO_DATE(param_in,’yyyy’);
? 6 ?END;
? 7 ?/

Function created.

dingjun123@ORADB> DROP TABLE t;
Table dropped.

dingjun123@ORADB> CREATE TABLE t(a VARCHAR2(10));
Table created.

dingjun123@ORADB> CREATE INDEX idx_t ON t(get_date(a));
Index created.

–2013-年5月份插入
dingjun123@ORADB> INSERT INTO t VALUES(’2013′);
1 row created.

dingjun123@ORADB> commit;
Commit complete.

dingjun123@ORADB> SELECT * FROM t WHERE get_date(a)=DATE’2013-5-1′;
A
———-
2013
1 row selected.

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

–2013年6月份插入
dingjun123@ORADB> INSERT INTO t VALUES(’2013′);
1 row created.

dingjun123@ORADB> COMMIT;
Commit complete.

dingjun123@ORADB> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
Session altered.

dingjun123@ORADB> select a from t;
A
———-
2013
2013
2 rows selected.

?现在是查询：

dingjun123@ORADB> SELECT * FROM t WHERE get_date(a)=DATE’2013-5-1′;
A
———-
2013

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 1594971208
————————————————————————————-
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————-
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? ?16 | ? ? 1 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? | ? ? 1 | ? ?16 | ? ? 1 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX_T | ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“DINGJUN123″.”GET_DATE”(“A”)=TO_DATE(‘ 2013-05-01 00:00:00′,
? ? ? ? ? ? ? ‘syyyy-mm-dd hh24:mi:ss’))

? ???上面的结果是令人迷惑的，因为表里存储的有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下无影响，后来看了yangtingkun大师的文章，原来早就没有影响了。http://space.itpub.net/4227/viewspace-68620

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

???走全表扫描，函数会对每行都调用1次(当然DETERMINSTIC函数是可以有缓存效果的，以后再说明）：

dingjun123@ORADB> DROP TABLE tt;
Table dropped.

dingjun123@ORADB> CREATE TABLE ?tt(NAME VARCHAR2(10));
Table created.

dingjun123@ORADB> INSERT INTO tt
? 2 ? ? ?SELECT LEVEL FROM dual CONNECT BY LEVEL
999 rows created.

–DBMS_APPLICATION_INFO包监控函数的调用次数
dingjun123@ORADB> CREATE OR REPLACE FUNCTION func_tt(x IN VARCHAR2)
? 2 ?RETURN VARCHAR2 DETERMINISTIC AS
? 3 ?BEGIN
? 4 ? ? ?DBMS_APPLICATION_INFO.set_client_info(USERENV(‘client_info’)+1 );
? 5 ? ? ?RETURN ‘o’ || x;
? 6 ?END;
? 7 ?/
Function created.

dingjun123@ORADB> EXEC DBMS_APPLICATION_INFO.set_client_info(0);
PL/SQL procedure successfully completed.

dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME)
no rows selected

dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

? ?无函数索引，全表扫描，访问对每行都调用函数，一条SQL访问函数999次。如果使用函数索引，那么必然在创建（DML）的时候，会自动调用函数，索引中存储对应的key与函数运算结果值，所以，再使用到函数索引的时候，不用再调用函数，而且索引访问还提高效率，达到多种提高效率的效果。

–重置计数器
dingjun123@ORADB> EXEC DBMS_APPLICATION_INFO.set_client_info(0);
PL/SQL procedure successfully completed.

dingjun123@ORADB> CREATE INDEX ?idx_tt ON tt(func_tt(NAME));
Index created.

–创建索引的时候就调用函数了
dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

dingjun123@ORADB> set autotrace traceonly

–使用的时候不再调用函数，因为已经调用过函数，函数运算的结果已经存储到索引中了
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
1 row selected.

Execution Plan
———————————————————-
Plan hash value: 6977672
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| TT ? ? | ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX_TT | ? ? 4 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“DINGJUN123″.”FUNC_TT”(“NAME”)=’o1′)
Note
—–
? ?- dynamic sampling used for this statement (level=2)

Statistics
———————————————————-
? ? ? ? ?24 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ?14 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 417 ?bytes sent via SQL*Net to client
? ? ? ? 416 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 1 ?rows processed

dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

? ? 使用自定义函数索引是危险的，如果修改函数定义，没有rebuild或删除重建函数索引，那么函数索引中存储的还是旧的函数运算结果，这样会导致错误：

dingjun123@ORADB> CREATE OR REPLACE FUNCTION func_tt(x IN VARCHAR2)
? 2 ?RETURN VARCHAR2 DETERMINISTIC AS
? 3 ?BEGIN
? 4 ? ? ?DBMS_APPLICATION_INFO.set_client_info(USERENV(‘client_info’)+1 );
? 5 ? ???RETURN ‘a’ || x;
? 6 ?END;
? 7 ?/
Function created.

–查询不对，函数应该运算结果’o1′应该没有行，但是因为索引没有被rebuild
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
NAME
———-
1
1 row selected.

–强制全表扫描，正确
dingjun123@ORADB> SELECT/*+full(tt)*/ * FROM tt WHERE func_tt(NAME) = ‘o1′;

no rows selected

–rebuild索引后也正确
dingjun123@ORADB> alter index idx_tt rebuild;
Index altered.

dingjun123@ORADB> set autotrace traceonly
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
no rows selected

Execution Plan
———————————————————-
Plan hash value: 6977672
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| TT ? ? | ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX_TT | ? ? 4 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“DINGJUN123″.”FUNC_TT”(“NAME”)=’o1′)

? ? 在不得不使用函数索引来提高效率的时候，别忘记了，随时准备维护函数索引，而且别弄出奇奇怪怪的函数索引，导致乱七八糟的问题，那样就不好了！

原文地址：自定义函数索引使用及其注意点, 感谢原作者分享。