How to change explain plain to get your lucky index
                                                 Last update (2008-11-27 11:08:51)
                                                                                                                   Date added (2008-02-23 13:52:48)

It is very common to deal with queries and performance problems related to them. If all statistics related to tables, indexes, or partitions of the query have already been computed or estimated, the CBO has all the information about them and will decide an execution plan. But what can you do if this plan is not what you want!

For example the explain plan uses for "TableA" an index called "TableA_INDEX_N03" but you absolutely want to use the index "TableA_INDEX_N06" which is best by experience. What can you do to take your lucky index?

One could say: I 'll start estimating or computing new statistics for indexes, so the CBO at the end will use the best index. Good luck. CBO is a little bit stuborn and propably will continue to use the "TableA_INDEX_N03" index.

Another could say: I will use stored outlines. But this takes some time and effort.

I will propose a solution that might help: Delete statistics for the index that you want optimizer to use
analyze index TableA_INDEX_N06 delete statistics;
Check the explain plan again. You might get lucky. If not and still index TableA_INDEX_N06 is not used, you can follow the solution with the stored outlines.