Question: I have created a function-based index
on a table column, yet when I check by execution plan, the
function-based index is ignored by the optimizer.
Why does Oracle ignore a function-based index?
How do I force Oracle to use a function-based index?
Answer: : The optimizer is aware of all indexes
at index creation time, but you still must
analyze the
function-based index, else the optimizer may ignore the index.
The most common problem when a function-based index is ignored is
when the DBA has forgotten to analyze the function-based index.
After creating a function-based index, you want to re-gather table
statistics and get extended statistics on the function.
In this case we create a function-based index and then
analyze using the method_opt argument set to FOR ALL HIDDEN
COLUMNS.
Create an FBI on emp_nbr column with NULL values
create index
emp_null_emp_nbr_idx
on
emp
(nvl(ename,o));
To make the function-based index work, we must now analyze the
index. Depending on your release, these methods are appropriate:
EXEC
DBMS_STATS.gather_index_stats('EMP', 'emp_null_emp_nbr_idx');
exec
dbms_stats.gather_table_stats(
ownname=>null,
tabname=>
'EMP',
estimate_percent=>null,
cascade=>true,
method_opt=>
'FOR ALL HIDDEN COLUMNS SIZE 1′
);
In Oracle 11g and beyond, Oracle recommends
analyzing "extended" statistics on function-based indexes:
begin
dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'EMP',
method_opt => 'for all columns
size skewonly for columns (nvl(ename,o))'
);
end;
Also see these details on when a
function-based index is ignored.
