>> what could be the reason for the query to not to pick the FBIndx?
If you are using the default value of all_rows, the query my think that a full-table can is "better" than your function-based index. Remember, the all_rows optimizer mode optimizes to minimize computing resources, while the first_rows_n optimizer_mode optimizes to minimize response time.
If your function-based index has not been fully analyzed by dbms_stats, the optimizer may be ignoring the index. See here using statistics for indexes
It is rare, but you may need to check Oracle technical support for bugs relating to index usage.
You may have other indexes that Oracle perceives as being "better" for the query. To test this. use the SQL*Plus "set autotrace on" and "set timing on: commands and time the queries, once with the default and again using an index hint. The "best" query for response time is the one that fetches the rows with the fewest consistent gets.
>> Kindly suggest and please let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.You probably did not analyze the function-based index
, that's why it is not being invoked:
Try this first:http://www.dba-oracle.com/t_statistics_fun...based_index.htm
If you can't use a hint, adjusting stats amnd sewapping plans might be your only option.
But the big question is "why" the CBO did not choose your FBI? Testing to force Oracle to use an index: is easy:
- optimizer_index_cost_adj: If you are already using cost-based optimization and you have already run system stats, ty setting optimizer_index_cost_adj=10. This will temporarily force the use of the index:
alter session set optimizer_index_cost_adj=10;.
- Flush the shared pool: In a test environment you can also issue the "alter system flush shared pool" command to re-initialize all SQL.
alter system flush_shared_pool;.
Can you post your index create and plan?