One of the many new features in Oracle 8i is the
Function-Based Index (we will refrain from using
FBI, but only just). This allows the DBA to create
indexes on functions or expressions; these functions
can be user generated pl/sql functions, standard
SQL functions (non-aggregate only) or even a C callout.
A classic problem the DBA faces
in SQL Tuning is how to tune those queries that
use function calls in the where clause, and result
in indexes created on these columns not to be used.
Example
Standard B-Tree index on SURNAME
with cost based optimizer
create index non_fbi on sale_contacts
(surname);
analyze index non_fbi compute statistics;
analyze table sale_contacts compute statistics;
SELECT count(*) FROM sale_contacts
WHERE UPPER(surname) = 'ELLISON';
create index
fbi on sale_contacts (UPPER(surname));
analyze index fbi compute statistics;
analyze table sale_contacts compute statistics;
SELECT count(*) FROM sale_contacts WHERE UPPER(surname)
= 'ELLISON';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
Bytes=17)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=2
Card=381 Bytes=6477)
The function-based index has
forced the optimizer to use index range scans (retuning
zero or more rowids) on the surname column rather
than doing a full table scan (non-index lookup).
Optimal performance does vary depending on table
size, uniqueness and selectivity of columns, use
of fast full table scans etc. Therefore try both
methods to gain optimal performance in your database.
It is important to remember that
the function-based B*Tree index does not store the
expression results in the index but uses an "expression
tree". The optimizer performs expression matching
by parsing the expression used in the SQL statement
and comparing the results against the expression-tree
values in the function-based index. This comparison
IS case sensitive (ignores spaces) and therefore
your function-based index expressions should match
expressions used in the SQL statement where clauses.
Init.ora Parameters
The following parameter must
be set in your parameter file: QUERY_REWRITE_INTEGRITY
= TRUSTED
QUERY_REWRITE_ENABLED = TRUE
COMPATIBLE = 8.1.0.0.0 (or higher)
Grants
Grants To create function-based
indexes the user must be granted CREATE INDEX and
QUERY REWRITE, or alternatively be granted CREATE
ANY INDEX and GLOBAL QUERY REWRITE. The index owner
must have EXECUTE access on the function used for
the index. If execute access is revoked then the
function-based index will be "disabled" (see dba_indexes).
Disabled Indexes
If your function-based index has
a status of "disabled" the DBA can do one of the
following:
a) drop and create the index (take note of its current
settings)
b) alter index enable, function-based
indexes only, also use disable keyword as required
c) alter index unusable.
Queries on a DISABLED index fail
if the optimizer chooses to use the index.Here is
an example ORA error:
ERROR at line 1: ORA-30554: function-based index
MYUSER.FBI is disabled.
All DML operations on a DISABLED
index also fail unless the index is also marked
UNUSABLE and the initialization parameter SKIP_UNUSABLE_INDEXES
is set to true.
Some more Examples
CREATE INDEX
expression_ndx
ON mytable ((mycola + mycolc) * mycolb);
SELECT mycolc FROM mytable
WHERE (mycola + mycolc) * mycolb <= 256;
..or a composite index..
CREATE INDEX example_ndx
ON myexample (mycola, UPPER(mycolb), mycolc);
SELECT mycolc
FROM myexample
WHERE mycola = 55 AND UPPER(mycolb) = 'JONES';
Restriction & Rule Summary
The following restrictions apply
to function based indexes. You may not index:
a) LOB columns
b) REF
c) Nested table column
d) Objects types with any of the above data types.
Function-based indexes must always
follow these rules:
a) Cost Based optimizer only, must generate statistics
after the index is created
b) Can not store NULL values (function can not return
NULL under any circumstance)
c) If a user defined pl/sql routine is used for
the function-based index, and is invalidated, the
index will become "disabled"
d) Functions must be deterministic (always return
the same value for a known input)
e) The index owner must have "execute" access on
function used in the function-based index. Revocation
of the privilege will render the index "disabled"
f) May have a B-Tree and Bitmap index type only
g) Can not use expressions that are based on aggregate
functions, ie. SUM, AVG etc.
h) To alter a function-based index as enabled, the
function used must be valid, deterministic and the
signature of the function matches the signature
of the function when it was created.
