函数索引的使用

官方文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14211/data_acc.htm#i9946

15.2 Using Function-based Indexes forPerformance

 

A function-based index includes columns thatare either transformed by a function, such as theUPPER function, or included in an expression, such ascol1 +col2. With a function-based index,you can store computation-intensive expressions in the index.

 

Defining afunction-based index on the transformed column or expression allowsthat data to be returned using the index when that functionor expression is used in aWHERE clause or anORDER BY clause.--当函数或者表达式用于WHERE或者ORDER BY子句当中，

定义在转换列或者表达式上的一个表达式函数允许使用索引返回数据。

 

This allows Oracle to bypass computing the value of theexpression when processingSELECT andDELETE statements. Therefore, a function-based index can be beneficial whenfrequently-executed SQL statements include transformed columns, orcolumns in expressions, in aWHERE orORDER BY clause.--用于WHERE或者ORDER BY子句当中

 

Oracle treats descending indexes as function-based indexes. Thecolumns marked DESC are sorted in descendingorder.

For example, function-based indexes defined with theUPPER(column_name) orLOWER(column_name) keywords allowcase-insensitive searches. The index created in the followingstatement:

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

facilitates processing queries such as:

SELECT * FROM employees    WHERE UPPER(last_name) = 'MARKSON';

文档地址：http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_indexing.htm#i1006463

Restrictions for Function-Based Indexes

--对于基于函数的索引的限制 Note the following restrictions for function-based indexes:

• Only cost-based optimization can use function-based indexes.Remember to call DBMS_STATS.GATHER_TABLE_STATISTICS orDBMS_STATS.GATHER_SCHEMA_STATISTICS, for thefunction-based index to be effective.

• Any top-level or package-level PL/SQL functions that are used inthe index expression must be declared asDETERMINISTIC. That is, they always return the sameresult given the same input, for example, theUPPERfunction. You must ensure that the subprogram really isdeterministic, because Oracle Database does not check that theassertion is true.

  The following semantic rules demonstrate how to use the keywordDETERMINISTIC:

  • You can declare a top level subprogram asDETERMINISTIC.

  • You can declare a PACKAGE level subprogram asDETERMINISTIC in thePACKAGEspecification but not in the PACKAGEBODY. Errors are raised ifDETERMINISTICis used inside a PACKAGE BODY.

  • You can declare a private subprogram (declared inside anothersubprogram or a PACKAGE BODY) asDETERMINISTIC.

  • A DETERMINISTIC subprogram can call anothersubprogram whether the called program is declared asDETERMINISTIC or not.

• If you change the semantics of a DETERMINISTICfunction and recompile it, then existing function-based indexes andmaterialized views report results for the prior version of thefunction. Thus, if you change the semantics of a function, you mustmanually rebuild any dependent function-based indexes andmaterialized views.

• Expressions in a function-based index cannot contain anyaggregate functions. The expressions should reference only columnsin a row in the table.

• You must analyze the table or index before the index isused.

• Bitmap optimizations cannot use descending indexes.

• Function-based indexes are notused when OR-expansion is done.

• The index function cannot be marked NOT NULL. Toavoid a full table scan, you must ensure that the query cannotfetch null values.

• Function-based indexes cannot use expressions that returnVARCHAR2 orRAW data types of unknownlength from PL/SQL functions. A workaround is to limit the size ofthe function's output by indexing a substring of known length:

  -- INITIALS() might return 1 letter, 2 letters, 3 letters, and so on.-- We limit the return value to 10 characters for purposes of the index.CREATE INDEX func_substr_index ON  emp_tab(substr(initials(ename),1,10);-- Call SUBSTR both when creating the index and when referencing-- the function in queries.SELECT SUBSTR(initials(ename),1,10) FROM emp_tab;