Function based indexes

WHERE clauses that modify the value of an indexed column will not use an index. Similarly, WHERE clauses that modify the value of a cluster key column will not perform a hash cluster scan.

For example:

In order to use an index or hash cluster scan, the indexed column / cluster key must appear unaltered in the WHERE clause. Often it is possible to overcome this problem by restructuring your WHERE clause

If your query cannot be restructured and the query is critical to your system, you may be able to use a function based index. A function based index allows you to create an index over a function of column instead of the column itself.


See the CREATE INDEX command in the Oracle SQL Reference documentation for more information on Function Based Indexes.

©Copyright 2003