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 theExplain 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 thetable absent matches with the Explain Plan for the 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 expect Cost based optimizer to be intelligent than the RULE based.
Well the cost based optimizer is more intelligent but that doesnt mean its perfect, occasionally the rule based one will be better (especially if the app has been tuned for the rule based optimizer). After all the optimizer is guessing at the best way of exectuing your query given what your data is and how your database has been configured.
Things you can do are
1) use hints to get it to use the right execution plan or modify the sql so it favours a particular execution path.
2) use different options on your analyze to do a more detailed analysis i.e. for all indexed columns size <n>, look up the analyze command or dbms_stats.
3) alter your index i.e. rebuild it so it might be more efficient to use the index, or compress it.
4) look at your init.ora parameters like optimizer* and db_file_multiblock_read_count (smaller values favour indexes).
5) gather system stats using dbms_stats so the database has a better idea of the cost of table reads versus index reads.
Dont give up as in oracle you have many ways of getting to your goal, it just takes time.