Databases Reference
In-Depth Information
Tip
Behind the scenes, the Oracle Database represents expressions of function-based indexes as virtual
columns. This enables you to gather statistics on these indexes. The database can also build histograms on such
virtual columns.
Collecting Statistics for Function-Based Indexes
Oracle automatically collects statistics for an index upon its creation and thus you don't have to
explicitly collect fresh statistics after creating an index. However, in the case of a function-based index,
things work a bit differently. Oracle Database creates a hidden virtual column on the parent table when
it creates a function-based index. The database creates this virtual column on the table to help the
optimizer more accurately determine the function's selectivity and cardinality, thus helping it compute
a more accurate cost of using the function-based index you've created.
When you create a function-based index, Oracle automatically computes index statistics such as the
number of leaf blocks, BLEVEL, and clustering factor, but it won't compute other more critical statistics
such as the number of distinct values (NDV) associated with the virtual column. The following example
illustrates this fact:
SQL> create index emp_total_sal_idx
on employees (12 * salary * commission_pct, salary, commission_pct);
Index created.
SQL>
Once you create the function-based index as shown here, check the statistics on the new hidden
virtual column created by the database.
SQL> select column_name,num_distinct, hidden_column,virtual_column
from dba_tab_ cols where table_name='EMPLOYEES';
COLUMN_NAME NUM_DISTINCT HID VIR
------------------- ------------ ---- ----
SYS_NC00012$ YES YES
DEPARTMENT_ID 11 NO NO
MANAGER_ID 18 NO NO
…
12 rows selected.
SQL>
As the query's output shows, a new virtual column (
SYS_NC00012$
) was created following the
creation of the function-based index. This column is both virtual and hidden. The
NUM_DISTINCT
column
is empty, meaning that the database has no idea of the selectivity associated with this function-based
index. The cost-based optimizer may thus end up with wrong execution plans, even though it uses your
new function-based index. To avoid this problem, you must always collect statistics on the hidden
virtual column after creating a function-based index.
