函数索引

一 函数索引的适用场合     

        函数索引用适用于这样的情况：被索引的列或列集合包含在某个表达式或是函数中（后面简称表达式），而这个表达式又出现在WHERE子句或ORDER BY子句中。 

        假设表some_table包含a,b,c三个字段，并且经常要执行下面的查询： 

        SELECT a

        FROM some_table

        WHERE a + b * (c - 1) < 100; 

        为了加速查询，可以在some_table上创建这样的函数索引： 

        CREATE INDEX idx ON some_table (a + b * (c - 1));        

        当执行SELECT和DELETE语句时，Oracle将绕过计算表达式的过程，加速了语句的执行速度。但是如果是执行INSERT或是UPDATE语句，Oracle就必须计算表达式，更新索引。另外，如果函数索引表达式未出现在WHERE子句或ORDER BY子句中，基于成本的优化就只能进行全表扫描。函数索引也就派不上用场了。 

        综上所述，如果查询条件为一个表达式，且大部分操作都是查询或删除的话，就可以考虑在表上添加函数索引。 

二  如何使用函数索引 

        从上面的例子已经看到，使用函数索引，分两个步骤：

        （1）创建函数索引。

        （2）在查询的WHERE子句或ORDER BY子句中指定函数索引的表达式。 

        要使用函数索引，还需保证下面两个初始化参数正确设置：

        （1）QUERY_REWRITE_ENABLED设置为TRUE。

        （2）QUERY_REWRITE_ENABLE设置为ENFORCED以外的值。 

三  哪些表达式可以用于函数索引 

        可用于构建函数索引的表达式可以是算术表达式，包括PL/SQL函数、包函数、C callout、SQL函数的表达式。表达式不能包含任何聚合函数，并且表达式必须是确定性的。

        如果要在对象类型列上构建函数索引，可以使用对象的方法，例如MAP方法。 

四  哪些列上不能创建函数索引 

        LOB列，REF列，嵌套表列。如果对象类型包含LOB，REF，或是嵌套表类型，则也不能在该类型的列上创建函数索引。 

五 使用函数索引的注意事项 

        （1）在使用函数索引时，必须确保表达式不能返回NULL值。

        （2）如果表达式依赖的函数变的无效或是被删除，Oracle会将索引标记为DISABLED，在DISABLED索引上的DML和查询操作都会失败。