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.
InstanceKey varchar2(255),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.
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?