oracle 函數索引(2)

Oracle8i 函数索引实验

===========================================================

作者: oracle(http://oracle.itpub.net)
发表于: 2006.07.17 20:40
分类: 一般分类
出处: http://oracle.itpub.net/post/20957/146498
---------------------------------------------------------------

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,则允许.