I have observed something strange.
If I delete statistics of a table the query runs much faster than how it would run with the statistics present.
With the statistics present for the table , Explain Plan does not show the correct index usage while after deleting statistics , the correct index choice is seen in teh Explain Plan.
I understand in the absence of statistics , optimizer becomes RULE based as I saw that the Explain Plan of the SQL query with statistics of teh table absent matches with the Explain Plan for teh query with the hint as RULE .
But why is it that the correct index is not being chosen by the optimizer even though I dropped and recreated the index and then analyzed the table.
I expext Cost based optimizer to be intelligent than teh RULE based.
The CBO isn't infallible, it works based on statistical analysis of the data. If it decided not to use the index then its statistics must suggest that the index isn't selective enough to perform better than the full scan.
You may want to read about the optimizer_index_cost_adj parameter on AskTom.
As Andrew said .. It may be that the index is not the best choice... What is the clustering factor of the index ... analyze index validate structure...
select * From index_stats;
You may see that the index clustering is such that this number is close to the number of rows in the table ... not real good ... If this is the case, more than likely, you will have to read more data blocks to get to the data ...
Also you said that you analyzed the table ... how about the index ???
I am aware clustering fator should be preferably close to the no. of blocks and not no. of rows of the table. The index was dropped and recreated and the analyze table was done with cascade option. Histogram for columns were also collected but still teh CBO dint choose it.
Pls note that the index is good coz RULE cased optimizer uses it and the SQL runs much faster with that index . Its just that CBO doesn't prefer to use it.
As Andrew said, I am working with the article suggested.