Database Reference
In-Depth Information
Obviously, you want to take advantage of an index if a restriction leads to strong selectivity. For that purpose, if it
isn't possible to modify the
WHERE
clause or specify constraints, you can create a
function-based index
. Simply put, this
is an index created on the return value of a function or the result of an expression. Here's an example:
SQL> CREATE INDEX i_c1_upper ON t (
upper(c1)
);
SQL> SELECT * FROM t WHERE upper(c1) = 'SELDON';
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 4 |
|* 2 | INDEX RANGE SCAN | I_C1_UPPER | 4 | 4 |
--------------------------------------------------------------------
2 - access(UPPER("C1")='SELDON')
■
Function-based indexes based on functions that take literals as parameter aren't selected by the query
optimizer when the
cursor_sharing
initialization parameter is set to either
force
or
similar
. that's because the literals
are replaced by bind variables. the
fbi_cs.sql
script demostrates such a case.
Caution
Another problem related to the utilization of functions and expressions in
WHERE
clauses is that the query
optimizer, as described in the “Extended Statistics” section in Chapter 8, misestimates the cardinality of the result set
produced by the row source operations applying them. The examples in this section (notice the
E-Rows
and
A-Rows
columns in the execution plans) illustrate that with a function-based index in place, the query optimizer can also
improve the estimations it makes. And that improvement can occur independently of whether a function-based index
is used to access data. More accurate estimations are possible because each function-based index adds a hidden
column to the table it's created on. Because column statistics and histograms are gathered for a hidden column as for
any other column, the query optimizer gets additional information that wouldn't be available without a function-based
index. It's also essential to point out that the object statistics at the table level for the new hidden column aren't
gathered while the function-based index is built. Only index statistics are automatically gathered. Therefore, you
shouldn't forget to gather the object statistics at the table level after creating a new function-based index.
Function-based indexes can also be created on user-defined functions written in PL/SQL. The only requirement
is that the function must be defined as
DETERMINISTIC
.
■
Function-based indexes based on a user-defined function aren't invalidated or made unusable when
the pL/SQL code they depend on is changed. that, of course, may lead to wrong results. If you change the code of such
a function, you should immediately rebuild the dependent index. an example of this behavior is available in the
fbi_udf.sql
script.
Caution
As of version 11.1, to avoid repeating a function or expression in the index as well as in several or possibly many
SQL statements, it's possible to create a virtual column based on the function or expression. In this way, the index
