14.2 Using Function-based Indexes for Performance 使用基于函数的索引

原文：

A function-based index includescolumns that are either transformed by a function, such as the UPPER function, or included in an expression, such as col1 +col2. With afunction-based index, you can store computation-intensive expressions in theindex.

Defining a function-based index onthe transformed column or expression allows that data to be returned using theindex when that function or expression is used in a WHERE clause or an ORDER BY clause. This allows Oracle Database to bypass computingthe value of the expression when processing SELECT andDELETE statements.Therefore, a function-based index can be beneficial when frequently-executedSQL statements include transformed columns, or columns in expressions, ina WHERE or ORDER BY clause.

Oracle Database treats descendingindexes as function-based indexes. The columns marked DESC are sorted in descending order.

For example, function-based indexesdefined with the UPPER(column_name) or LOWER(column_name) keywords allow case-insensitive searches. The index createdin the following statement:

 

CREATE INDEXuppercase_idx ON employees (UPPER(last_name));

 

facilitates processing queries such as:

SELECT * FROMemployees
    WHERE UPPER(last_name) = 'MARKSON';

See Also:

• Oracle Database Advanced Application Developer's Guide and Oracle Database Administrator's Guide for more information on using function-based indexes

• Oracle Database SQL Language Reference for more information on the CREATE INDEX statement

 

源文档 <http://docs.oracle.com/cd/E11882_01/server.112/e41573/data_acc.htm#PFGRF94785>

 

 

译文：

基于函数的索引或者是包含一个函数转化列，比如UPPER函数，或者是包含写在一个表达式中的列，比如col1+col2的情况。使用基于函数的索引，你可以把经常运算的表达式写在索引中。

创建基于函数的索引后，在语句的WHERE子句或者ORDER BY子句中如果使用了这些函数，那么就会使用这个索引来返回数据。这使得Oracle在处理SELECT和DELETE语句时可以绕过计算表达式的值的操作。因此，如果在经常执行的语句的WHERE或者ORDERBY 语句中包含表达式或者函数，那么建立基于函数的索引是有好处的。

Oracle 把降序索引当做基于函数的索引处理，标记为DESC的列会按照降序排序。

 

例如，使用UPPER(column_name) 或者 LOWER(column_name) 创建的索引允许不区分大小写的查找。下面是创建索引的语句：

 

CREATE INDEX  uppercase_idx ON employees (UPPER(last_name));

 

方便处理查询，如：

SELECT * FROMemployees
    WHERE UPPER(last_name) = 'MARKSON';