oracle保证用函数但索引不失效,使用Oracle函数索引简单介绍（一般）

如果一个函数(function)或表达式(expression)使用了一个表的一列或多 列，则用户可以依据这些函数或表达式为表建立索引，这样的索引被称为函 数索引(Function-Based Index)。函数索引能够计算出函数或表达式的， 并将其保存在索引中。用户创建的函数索

如果一个函数(function)或表达式(expression)使用了一个表的一列或多

列，则用户可以依据这些函数或表达式为表建立索引，这样的索引被称为函

数索引(Function-Based Index)。函数索引能够计算出函数或表达式的值，

并将其保存在索引中。用户创建的函数索引既可以是平衡树类型(B-tree

index)的，也可以是位图类型(bitmap index)的。

用于创建索引的函数可以是一个数学表达式(arithmetic expression)，也可以

是使用了 PL/SQL 函数(PL/SQL function)，包函数(package function)，C

外部调用(C callout)，或 SQL 函数(SQL function)的表达式。用于创建索

引的函数不能包含任何聚合函数(ggregate function)，如果为用户自定义函

数，则在声明中必须使用 DETERMINISTIC 关键字。例如，

create or replace function f_david(p_value varchar2) return varchar2

deterministicis

begin

return p_value;

end;

如果在一个使用对象类型(object type)的列上建立函数索引，则可以使用此对象的方法(method)

作为函数，例如此对象的 map 方法。用户不能在数据类型为 LOB，REF，或嵌套表(nested table)的列上

建立函数索引，也不能在包含 LOB，REF，或嵌

套表等数据类型的对象类型列上建立函数索引。

使用函数索引

如果一个 SQL 语句的 WHERE 子句中使用了函数，那么建立相应的函数索引

(function-based index)是提高数据访问性能的有效机制。表达式

(expression)的结果经过计算后将被存储在索引中。但是当执行 INSERT 和

UPDATE 语句时，Oracle 需要进行函数运算以便维护索引。

例如，如果用户创建了以下函数索引：

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

当 Oracle 处理如下查询时就可以使用之前建立的索引：

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

使用 UPPER(column_name) 或 LOWER(column_name) 函数建立函数索引

(function-based index)有助于与大小写无关(case-insensitive)的查询。例如

创建以下函数索引：

CREATE INDEX uppercase_idx ON employees (UPPER

(first_name));

有助于提高以下查询的性能：

SELECT * FROM employees WHERE UPPER(first_name) =

'RICHARD';

函数索引的依赖性

函数索引(function-based index)依赖于索引定义表达式中使用的函数。如果

此函数为 PL/SQL 函数(PL/SQL function)或包函数(package function)，当

函数声明(function specification)发生变化时，索引将失效(disabled)。

用户需要被授予(grant)CREATE INDEX 或 CREATE ANY INDEX 权限才能

创建函数索引(function-based index)。

要想使用函数索引(function-based index)：

● 建立索引后，表必须经过分析(analyze)。

● 必须保证查询的条件表达式不是 NULL 值, 因为 NULL 值不会被存储到

索引中。

以下各节将讲述使用函数索引的其他需求。

定义函数的权限

函数索引(function-based index)的所有者(owner)必须具备此索引定义中

使用的函数的 EXECUTE 权限。当 EXECUTE 权限被收回(revoke)后，

Oracle 则将索引标识为 DISABLED。索引的所有者无须具备此函数的

EXECUTE WITH GRANT OPTION 权限，即可将索引所在表的 SELECT 权限

授予(grant)其他用户。

解决函数索引的依赖性问题

函数索引(function-based index)依赖于她使用的所有函数。如果函数或函数

所在包的声明(specification)被修改过(或索引所有者对函数的 EXECUTE

权限被收回)，将会出现以下情况：

● 索引被标记为 DISABLED。

● 如果优化器(optimizer)选择了在标记为 DISABLED 的索引上执行查

询，那么此查询将失败

● 使用标记为 DISABLED 的索引而执行的 DML 操作将失败，除非此索引

同时被标记为 UNUSABLE 且初始化参数(initialization parameter)

SKIP_UNUSABLE_INDEXES 被设为 TRUE。

函数被修改之后，用户可以使用 ALTER INDEX ... ENABLE 语句将索引重新置

为 ENABLE 状态。

本文原创发布php中文网，转载请注明出处，感谢您的尊重！