合 Oracle之函数索引
Tags： Oracle
在Oracle中，有一类特殊的索引，称为函数索引（Function-Based Indexes，FBI），它基于对表中列进行计算后的结果创建索引。函数索引在不修改应用程序的逻辑基础上提高了查询性能。如果没有函数索引，那么任何在列上执行了函数的查询都不能使用这个列的索引。当在查询中包含该函数时，数据库才会使用该函数索引。函数索引可以是一个B-Tree索引或位图索引。
用于生成索引的函数可以是算术表达式，也可以是一个包含SQL函数、用户定义PL/SQL函数、包函数，或C调用的表达式。当数据库处理INSERT和UPDATE语句时，它仍然必须计算函数才能完成对语句的处理。
对于函数索引的索引列的函数查询可以通过视图DBA_IND_EXPRESSIONS来实现，通过如下的SQL语句可以查询所有的函数索引：
SELECT * FROM DBA_INDEXES D WHERE D.INDEX_TYPE LIKE 'FUNCTION-BASED%';
函数索引必须遵守下面的规则：
① 必须使用基于成本的优化器，而且创建后必须对索引进行分析。
② 如果被函数索引所引用的用户自定义PL/SQL函数失效了或该函数索引的属主没有了在函数索引里面使用的函数的执行权限，那么对这张表上的执行的所有的操作（例如SELECT查询、DML等）也将失败（会报错：ORA-06575: Package or function F_R1_LHR is in an invalid state或ORA-00904: : invalid identifier）。这时，可以重新修改自定义函数并在编译无报错通过后，该表上所有的DML和查询操作将恢复正常。
③ 创建函数索引的函数必须是确定性的。即，对于指定的输入，总是会返回确定的结果。
④ 在创建索引的函数里面不能使用SUM、COUNT等聚合函数。
⑤ 不能在LOB类型的列、NESTED TABLE列上创建函数索引。
⑥ 不能使用SYSDATE、USER等非确定性函数。
⑦ 对于任何用户自定义函数必须显式的声明DETERMINISTIC关键字，否则会报错：“ora-30553: the function is not deterministic”。
需要注意的是，使用函数索引有几个先决条件：
（1）必须拥有CREATE INDEX和QUERY REWRITE（本模式下）或CREATE ANY INDEX和GLOBAL QUERY REWRITE（其它模式下）权限。其赋权语句分别为“GRANT QUERY REWRITE TO LHR;”和“GRANT GLOBAL QUERY REWRITE TO LHR;”。
（2）必须使用基于成本的优化器，基于规则的优化器将被忽略。
（3）参数QUERY_REWRITE_INTEGRITY和QUERY_REWRITE_ENABLED可以保持默认值。
QUERY_REWRITE_INTEGRITY = ENFORCED
QUERY_REWRITE_ENABLED = TRUE（从Oracle 10g开始默认为TRUE）
这里举一个基于函数的索引的例子。
首先为函数索引的建立及数据做准备：
SYS@lhrdb> CREATE TABLE TESTFINDEX_LHR(ID NUMBER,SCHR VARCHAR2(10));
Table created.
SYS@lhrdb> CREATE INDEX IND_FUN ON TESTFINDEX_LHR(UPPER(SCHR));
Index created.
SYS@lhrdb> INSERT INTO TESTFINDEX_LHR VALUES(1,'a');
1 row created.
SYS@lhrdb> COMMIT;
Commit complete.
因为强制使用基于规则的优化器，所以，不会使用函数索引：
SYS@lhrdb> SELECT /+ RULE/ * FROM TESTFINDEX_LHR WHERE UPPER(SCHR)='A';
ID SCHR
1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 940247041
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TESTFINDEX_LHR |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("SCHR")='A')
Note
-----
- rule based optimizer used (consider using cbo)
这里优化器选择了全表扫描，若在不使用基于规则的优化器的情况下，则该查询会选择函数索引IND_FUN：
SYS@lhrdb> SELECT * FROM TESTFINDEX_LHR WHERE UPPER(SCHR)='A';
ID SCHR
1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 967513602
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTFINDEX_LHR | 1 | 27 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_FUN | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("SCHR")='A')
Note
-----
- dynamic sampling used for this statement (level=2)
SYS@lhrdb> SELECT D.TABLE_NAME,D.COLUMN_EXPRESSION FROM DBA_IND_EXPRESSIONS D WHERE D.INDEX_NAME='IND_FUN';
TABLE_NAME COLUMN_EXPRESSION
TESTFINDEX_LHR UPPER("SCHR")
可见，例子中使用了IND_FUN函数索引，且函数可以通过视图DBA_IND_EXPRESSIONS来查询。
我们进行数据库检索优化的方法，通常是对特定条件列加索引，减少由于全表扫描带来的逻辑物理IO消耗。索引的种类很多，我们经常使用的B树索引，由于结构简单、适应性强，可以应对大多数数据访问优化需求。除B树索引外，其他一些索引类型，也在一些场合中扮演着独特的地位。本篇来介绍其中的函数索引。
1**、从B*树索引的失效谈起**
和通常一样，我们准备实验环境。
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> create table t as select * from dba_objects;
Table created
//构建两个索引用作实验对象
SQL> create index idx_t_owner on t(owner);
Index created
SQL> create index idx_t_ddlt on t(last_ddl_time);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
