Unexpected Index Used

I am having some trouble with a query running in Oracle 11. An index was created to improve the performance of the query. However the database seems to be determined to use the primary key index.

Some of the table structure.

FOO_BAR_TABLE

InstanceKey varchar2(255),
CreateDateTime date,
ObjectClass varchar2(96),
AssignedTo varchar2(128),
AssignmentElapsedTime number(18),
ObjectData BLOB

NDX_PK on FOO_BAR_TABLE(InstanceKey)

NDX_EXTRACT on FOO_BAR_TABLE(CreateDateTime, InstanceKey)

The query executing is a 3rd-party generated query based on a couple of parameters and the NDX_EXTRACT index was created to help the query. Since it is 3rd-party generated there is not an easy way to add hints to recommend the NDX_EXTRACT index.

select count(InstanceKey)
from FOO_BAR_TABLE
where InstanceKey like 'HISTORY-FOO-BAR%'
and CreateDateTime >= to_date('01/21/2013 12:00:00 am', 'mm/dd/yyyy hh:mi:ss am')
and CreateDateTime <= to_date('01/22/2013 11:59:59 pm', 'mm/dd/yyyy hh:mi:ss am')
and InstanceKey like 'HISTORY-FOO-BAR FOO-BAR-OBJECT PFIX%'

However when the explain plan is request on this query the NDX_PK is determined the best option. One additional note, the table currently has little over 34 million records.

I would like to understand the following.

Why is the database consistently recommending the NDX_PK index instead of the NDX_EXTRACT index?
Is there something in the database that is missing or needs to be done in order to change the recommendation?

Quote:

---

Originally Posted by jasoncollum

I am having some trouble with a query running in Oracle 11. An index was created to improve the performance of the query. However the database seems to be determined to use the primary key index.

Some of the table structure.

FOO_BAR_TABLE

InstanceKey varchar2(255),
CreateDateTime date,
ObjectClass varchar2(96),
AssignedTo varchar2(128),
AssignmentElapsedTime number(18),
ObjectData BLOB

NDX_PK on FOO_BAR_TABLE(InstanceKey)

NDX_EXTRACT on FOO_BAR_TABLE(CreateDateTime, InstanceKey)

The query executing is a 3rd-party generated query based on a couple of parameters and the NDX_EXTRACT index was created to help the query. Since it is 3rd-party generated there is not an easy way to add hints to recommend the NDX_EXTRACT index.

select count(InstanceKey)
from FOO_BAR_TABLE
where InstanceKey like 'HISTORY-FOO-BAR%'
and CreateDateTime >= to_date('01/21/2013 12:00:00 am', 'mm/dd/yyyy hh:mi:ss am')
and CreateDateTime <= to_date('01/22/2013 11:59:59 pm', 'mm/dd/yyyy hh:mi:ss am')
and InstanceKey like 'HISTORY-FOO-BAR FOO-BAR-OBJECT PFIX%'

However when the explain plan is request on this query the NDX_PK is determined the best option. One additional note, the table currently has little over 34 million records.

I would like to understand the following.

Why is the database consistently recommending the NDX_PK index instead of the NDX_EXTRACT index?
Is there something in the database that is missing or needs to be done in order to change the recommendation?

---

Couple of questions:

1- Why two "InstanceKey like..." conditions on the predicate?
2- Have you gathered statistics on NDX_EXTRACT index?
3- Are there fresh statistics for the FOO_BAR_TABLE?
4- Have you tried an index only on CreateDateTime column?
5- How many rows out of the 34M rows are expected to be counted?

please post formatted EXPLAIN PLAN

"Since it is 3rd-party generated there is not an easy way to add hints to recommend the NDX_EXTRACT index."

you can run it in sqlplus with and without the hint to see what the difference is in time and cost. Specifically consistent gets and physical reads. You should do set timing on and set autotrace on to get time and explain plan. Once you understand which is faster you will be closer to understanding why Oracle is using the index that it is using. You should also look at stats as PAVB suggested. It might be that your index is less efficient, or that there is something about the way the query is written that prevents Oracle from using the index that you want.