使用Oracle函数索引简单介绍（一般）

最新推荐文章于 2024-08-28 12:22:16 发布

haiross

最新推荐文章于 2024-08-28 12:22:16 发布

阅读量5.9k

点赞数

分类专栏： Oracle索引文章标签：函数索引

Oracle索引专栏收录该内容

31 篇文章 1 订阅

订阅专栏

使用Oracle函数索引提高查询效率

2010-10-25 10:55 佚名互联网我要评论(0) 字号： T | T

想要提高Oracle数据库的查询检索的效率，使用函数索引无疑是一个非常好的方法。下文对Oracle函数索引的使用作了详尽的阐述，供您参考。

AD：

使用Oracle函数索引，无疑是提高查询效率的有效方法之一。下面就为您详细介绍Oracle函数索引的使用方法，希望对您能有所帮助。

谈到任何对列的操作都可能导致全表扫描，例如：

   
   select * from emp where substr(ename,1,2)=’SM’;

但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的基于Oracle函数索引，

   
   create index emp_ename_substr on eemp ( substr(ename,1,2) );

但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的Oracle函数索引，

   
   create index emp_ename_substr on eemp ( substr(ename,1,2) );

这样在执行上面的查询语句时，这个基于函数的索引将排上用场，执行计划将是（INDEX RANGE SCAN）。
上面的例子中，我们创建了基于函数的索引，但是如果执行下面的查询：

   
   select * from emp where substr(ename,1,1)=’S’

得到的执行计划将还是（TABLE ACCESS FULL），因为只有当数据列能够等式匹配时，基于函数的索引才能生效，这样对于这种索引的计划和维护的要求都很高。请注意，向表中添加索引是非常危险的操作，因为这将导致许多查询执行计划的变更。然而，如果我们使用基于函数的索引就不会产生这样的问题，因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引。

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

oracle函数索引

如果一个函数（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
deterministic is
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 状态。