【转载】ORACLE函数索引

    想要提高Oracle数据库的查询检索的效率，使用函数索引无疑是一个非常好的方法。下文对Oracle函数索引的使用作了详尽的阐述，供您参考。

        使用Oracle函数索引，无疑是提高查询效率的有效方法之一。下面就为您详细介绍Oracle函数索引的使用方法，希望对您能有所帮助。

        谈到任何对列的操作都可能导致全表扫描，例如：

select * from emp where substr(ename,1,2)='SM';

        但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的基于Oracle函数索引，

create index emp_ename_substr on eemp ( substr(ename,1,2) );

        这样在执行上面的查询语句时，这个基于函数的索引将排上用场，执行计划将是（INDEX RANGE SCAN）。

上面的例子中，我们创建了基于函数的索引，但是如果执行下面的查询：

select * from emp where substr(ename,1,1)=’S’

        得到的执行计划将还是（TABLE ACCESS FULL），因为只有当数据列能够等式匹配时，基于函数的索引才能生效，这样对于这种索引的计划和维护的要求都很高。

        请注意，向表中添加索引是非常危险的操作，因为这将导致许多查询执行计划的变更。然而，如果我们使用基于函数的索引就不会产生这样的问题，因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引

Oracle自8.1.5版开始支持基于函数的索引,但仅在8i的企业版和个人版中支持,标准版不支持.
使用基于函数的索引,可以在某此情况下加快查询速度.

下面以一个例子来详细说明.
病人姓名要求支持按简码查询,目前病人信息表没有简码这个字段,
如果在程序中直接调用zlspellcode(姓名)进行查询的话,会进行全表扫描,就比较慢.
如果要在不增加字段的情况下,实现索引查找,就可以利用函数索引来实现.

--先检查所用的Oracle是否支持函数索引(和位图索引一样,仅在企业版和个人版支持)

SQL> select * from v$version; 1 Oracle8i Enterprise Edition Release 8.1. 7.0. 0 - Production 2 PL/SQL Release 8.1. 7.0. 0 - Production 3 CORE 8.1. 7.0. 0 Production 4 TNS for 32-bit Windows: Version 8.1. 7.0. 0 - Production 5 NLSRTL Version 3.4. 1.0. 0 - Production

1.修改init.ora文件

加入
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
修改
compatible = 8.1.6
optimizer_mode = choose
(最好是cost,但需要分析所有的表,否则用来起慢)
重启Oracle.

2.授权

grant query rewrite to zlhis;

3.修改zlspellcode函数

在zlspellcode函数的is或as 之前加上这个关键字:deterministic

4.建立函数索引

create index 病人信息_IX_简码 on 病人信息(substr(zlspellcode(姓名), 1, 10));

注意,创建函数索引时,必须使用substr函数,因为自定义函数返回的是varchar2类型的,
否则会遇到ORA-01450错误,提示超过最大关键字长度

5.分析表

Analyze table 病人信息 Compute Statistics For Table For All Indexed Columns For All Indexes /

然后在pl/sql 中检查

Select * From 病人信息 Where substr(zlspellcode(姓名), 1, 10)='YHM';

F5看查询计划,是否用了索引
注意:查询语句的条件子句中必须使用substr(zlspellcode(姓名),1,10)才能用到索引,
因为索引是按此条件创建的,必须完全匹配.

以下是SQL Plus中查看的结果

SQL> set autotrace traceonly; SQL> Select * From 病人信息 Where substr(zlspellcode(姓名), 1, 10)='YHM';

索引的使用者必须能够有那个FBI索引上使用的那个函数(上例是:zlspellcode)的执行权限。
如果没有相应的权限，那么这个FBI索引得状态将变成DISABLED（可查DBA_INDEXES）。 
如果那个FBI索引得状态是DISABLED，那么DBA可以这样来处理： 
A：删除并重建 
B：ALTER INDEX index_name ENABLE。这个Enable选项只能对FBI索引使用。 注意,有些资料错误的写成ENABLED
C：ALTER INDEX UNUSABLE; 暂时停用该索引.(需要系统参数配合:SKIP_UNUSABLE_INDEXES)

注意：如果一个查询中使用到了这个索引，但是这个FBI索引的状态是DISABLED，但是优化器选择了使用这个索引，那么将会返回一个Oracle错误。 
例子： 
ORA error: 
ORA-01502: 索引'ZLHIS.病人信息_IX_简码'或这类索引的分区处于不可用状态

而且，一旦这个FBI索引的状态是Disabled，那么这张表上所有涉及索引列的DML操作也将失败。
除非这个索引得状态变成UNUSABLE,并且,在初始化参数里边指定SKIP_UNUSABLE_INDEXES为TRUE。

总结一下,要使用函数索引有以下要求或限制:

1.要求Oracle兼容参数在8.1.5以上
2.要求使用基于成本的优化器.(如果是Choose,则Oracle会自动对分析了的表选择cbo)
3.在自己的模式中的表上创建,须有系统特权query rewrite
在其它模式中的表上创建,须有系统特权global query rewrite
4.要求系统允许重写查询,以及信任一致性的输出
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
5.如果是自定义的函数(非系统函数),要求函数定义中加关键字:deterministic
6.要求函数具有一致性的输出结果,也就是说,同一函数进行相同的输入,总是得到唯一的输出.例如:函数中不能使用random输出数据.
7.如果有大量的插入和更新,函数索引将会影响性能,据实验,批量插入时,性能降低5倍左右
8.在8.1.7之前不能在函数中使用to_date函数,之后,支持一个解决办法,用关键字deterministic重写自己的to_date函数
9.如果函数是用户写的函数并且需要SQL引擎,则基于函数的索引不支持直接路径装载,例如:imp或exp中不支持使用direct=true参数
如果函数只是系统自带的,例如:upper,则允许.

转载于:https://www.cnblogs.com/fangquan/p/3965662.html