Update: See these
later articles on Oracle function-based indexes:
Using Function Based Indexes
One of the
projects I'm working on now involves tuning an Oracle 8i-based data
warehouse, that uses Discoverer as the user query tool. We've been
asked to improve the query response time for the warehouse, and one of
the first things we picked up on was the fact that most of the user
queries resulted in full table scans against the database tables.
For details of enhancements to
Function-Based Indexes in Oracle, check out this
additional article by Don Burleson.
Once an FBI is created, you need to create CBO
statistics, but beware that there are numerous bugs and issues when
analyzing a function-based index. See these important notes on
at the SQL being produced by Discoverer, the DDL for the various
tables, and the explain plans, it became apparent that whilst there
were indexes on most of the columns used in the WHERE clauses, the
indexes weren't being used because functions were being used on the
columns. For example, if a table called EMPLOYEES had an index on the
ENAME column and the following query
against the table, the index wouldn't get used as the UPPER() function
had been applied to it.
instance, a new feature called 'Function-Based
Indexes' that first appeared with Oracle 8i would help us out.
According to this
the many new features in Oracle 8i is the Function-Based Index . 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.
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.
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. "
In this case,
to create the Function-Based Index we need, we'd issue the following
CREATE INDEX fb_name_idx on employees (UPPER(name));
also points out a few prerequistes for working with Function-Based
After creating the Function-Based Index, you must ANALYZE it
‘FOR ALL HIDDEN COLUMNS SIZE 1′
then need to ANALYZE the table
TABLE employees COMPUTE STATISTICS;
users who will use the index need the Execute privilege on the
particular function used in the index, if it's a custom function
also need the CREATE INDEX (obviously) and QUERY REWRITE privilege
ANY INDEX, QUERY REWRITE TO user;
database needs to have the QUERY_REWRITE_INTEGRITY = TRUSTED,
QUERY_REWRITE_ENABLED = TRUE and COMPATIBLE = 220.127.116.11.0 (or higher)
parameters set, by using ALTER SYSTEM or amending the init.ora file.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.