基于函数的索引（ function-based index ）是 Oracle8.11.5 中增加的一种索引。

现在这已经是标准版的一个特性，但在 Oracle9i Release 2 之前的版本中，

这还只是企业版的一个特性。利用基于函数的索引，我们能够对计算得出的列建立索引，并在查询中使用这些索引。简而言之，利用这种能力，你可以做很多事情，如执行大小写无关的搜索或排序；根据复杂的公式进行搜索；还可以实现你自己的函数和运算符，然后在此之上执行搜索从而高效地扩展 SQL 语言。使用基于函数的索引可能有很多原因，其中主要的原因如下：

使用索引很容易实现，并能立即提交一个值。

可以加快现有应用的速度，而不用修改任何逻辑或查询。

在所有版本中，以下结论都适用：

使用基于代价的优化器（ cost-based optimizer ， CBO ）。在基于函数的索引中，虚拟列（应用了函数的列）只对 CBO 可见，而基于规则的优化器（ rule-based optimizer ， RBO ）不能使用这些虚拟列。

RBO 可以利用基于函数的索引中未应用函数的前几列。对于返回 VARCHAR2 或 RAW 类型的用户编写的函数，使用 SUBSTR 来约束其返回值，也可以

把 SUBSTR 隐藏在一个视图中（这是推荐的做法）。同样，下一节会给出这样一个例子。一旦满足前面的条件，基于函数的索引就很容易使用。只需使用 CREATE INDEX 命令来创建索引，优化器会在运行时发现并使用你的索引。

一个简单的例子：

SQL> insert into emp

2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)

3 select rownum empno,

4 initcap(substr(object_name, 1, 10)) ename,

5 substr(object_type, 1, 5) job,

6 rownum mgr,

7 created hiredate,

8 rownum sal,

9 rownum comm,

10 (mod(rownum, 4) + 1) * 10 deptno

11 from all_objects

RBO(Rule-Base-Optimizer)基于规则的优化器，执行结果为：

SQL> set autotrace traceonly explain

SQL> select * from emp where upper(ename)='PETER';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=RULE

1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE)

使用基于代价的优化器（ cost-based optimizer ， CBO ），执行结果为：

SQL> alter session set optimizer_mode=all_rows;

Session altered.

SQL> set autotrace traceonly explain

SQL> select * from emp where upper(ename)='PETER';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=2 Bytes=88)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car

d=2 Bytes=88)

2 1 INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' (INDEX) (Cost=1 Ca

rd=2)

基于函数的索引很容易使用和实现，他们能提供立即值。可以用基于函数的索引来加快现有应用的度，而不用修改应用中的任何逻辑或查询。通过使用基于函数的索引，可以观察到性能会呈数量级地增。使用这种索引能提前计算出复杂的值，而无需使用触发器。另外，如果在基于函数的索引中物化表达式，优化器就能更准确度估计出选择性。可以使用基于函数的索引有选择地只对感兴趣的几行建立索引。实际上，还可以对 WHERE 子句加索引。

ITPub博客

基于函数的索引

最新文章