官方文档地址: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 a WHERE
clause or anORDER
BY
clause.--当函数或者表达式用于WHERE或者ORDER BY子句当中,
定义在转换列或者表达式上的一个表达式函数允许使用索引返回数据。
This allows Oracle to bypass computing the value of theexpression when processing SELECT
andDELETE
statements. Therefore, a function-based index can be beneficial whenfrequently-executed SQL statements include transformed columns, orcolumns in expressions, in a WHERE
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 as
DETERMINISTIC
. That is, they always return the sameresult given the same input, for example, theUPPER
function. 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 keyword
DETERMINISTIC
:-
You can declare a top level subprogram as
DETERMINISTIC
. -
You can declare a
PACKAGE
level subprogram asDETERMINISTIC
in thePACKAGE
specification but not in thePACKAGE
BODY
. Errors are raised ifDETERMINISTIC
is used inside aPACKAGE
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
DETERMINISTIC
function 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 return
VARCHAR2
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;