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';
已选择16行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=71 Card=73 Bytes=7811)
1 0 TABLE ACCESS (BY INDEX ROWID) OF '病人信息' (Cost=71 Card=73 Bytes=7811)
2 1 INDEX (RANGE SCAN) OF '病人信息_IX_简码' (NON-UNIQUE) (Cost=1 Card=73)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
5078 bytes sent via SQL*Net to client
536 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
索引的使用者必须能够有那个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,则允许.
