| |
 |
|
Oracle Tips by Burleson |
Monitoring Functional
Indexes
The concept of a functional index was added
to Oracle8i. A functional index uses a function or collection of
functions to operate on its column, thus allowing the same function
or collection of functions to be applied in the WHERE clause of a
SELECT that uses the index. A simple example would be a functional
index using UPPER on a name field. This would allow selects of the
form:
See Code Depot
The new functionality will simplify table
and application design tremendously in applications that retrieve
data based on UPPER, LOWER, SOUNDEX, and other function-based
queries.
The
Remote DBA will want to know about, and track, the use of function-based
indexes in his or her database. The Remote DBA_INDEXES view has a new
column that makes this easier, the FUNCIDX_STATUS column. The
FUNCIDX_STATUS column contains NULL if the index is not a
function-based index, ENABLED if it is a function-based index and is
ready for use, and DISABLED if it is a function-based index that is
disabled and can’t be used. If the value in FUNCIDX_STATUS is not
NULL, a join to the Remote DBA_IND_EXPRESSIONS view will provide the
information on the expression used to create the function-based
column in the index.
www.oracle-script.com |
