Function based index Options

[kartik]

Hello,

One of our query is not using function based index, the required priv is granted to the user executing the query and also tables stats are gathered? what could be the reason for the query to not to pick the FBIndx? the table is a huge one having million of records, is it that CBO thinks that not picking FB indx is the best execution plan?

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.

Regards,
Kartik

[burleson]

Hi Katrik,

>> what could be the reason for the query to not to pick the FBIndx?

Good question!

Please read:

http://www.dba-oracle.com/t_index_not_using_index.htm

optimizer_mode: 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.
<li>dbms_stats: 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
<li>Bugs: It is rare, but you may need to check Oracle technical support for bugs relating to index usage.
<li>Other indexes: 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?

[burleson]

Hi Kartic,

For testing, were you able to force the DBI with an index hint?

I know you cannot use it in production, but you need to see if your index provided less consistent gets (using set autotrace on) than the existing index.

[kartik]

Hi Kartic,

For testing, were you able to force the DBI with an index hint?

I know you cannot use it in production, but you need to see if your index provided less consistent gets (using set autotrace on) than the existing index.

Thanks Don for your help, I did below and query is now picking the index

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

Then analysed the table and FB indx using dbs_stats as you suggested.

Query is now picking the indexes.

Thanks Again,
Kartik