Why is Oracle not using my index?

Today a person from development team told me that created an index on the Test environment and it is no used in his query. He does not understand that is not used. Actually one of the axioms of Oracle and in general for any RDBMS such as Sybase, MySQL, SQL Server, is “there is an index on the columns of selectivity does not imply that use it”.  Some of the reasons may be;

 

  • A query asking for all rows in the table. Traditionally in Oracle when a query is performed that returns more 20% of the rows, do a FULL SCAN,Oracle supposed to be less expensive FTS than access via index.
  • For parameters, there is parameters like OPTIMIZER_INDEX_COST_ADJ that can force more using indexes Vs full table scan or more FTS Vs Index.
  • There are other better indexes, or at least it may seem that you have more current statistics than index we hope use.

 

Summary;

If  it don’t take the index in a natural way, we would have to use a hint. If it gets naturally, maybe the next day may change, there is a new index, Statistics change …

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s