Optimizer depth - Index usage dependant on name of index

What parameters govern the depth of the optimisation?

I have a table with four indexes defined. My developer has some SQL which accesses the table and has a subselect to the same table. The subselect could be improved with a fifth index.

If I define the index with a name which is alphabetically higher than the existing four indexes then it is not used. If the index name is alphabetically lower then it is used!

The impression I get is that when the index is higher the optimizer gives up before it finds the fifth index.

The columns in the new index are duplicates of columns in another index, just in a different order. I have seen comments in performance tuning manuals (V7?) which suggest an index will be ignored if the columns are the same as another index.

When the index is working it reduces a 88 minutes job to 36 seconds.

I am using Oracle 8.05 Sun/Solaris 2.6

I have checked manuals / metallink etc. and logged it with Oracle.

FYI: I tried varying optimiser_search_limit from the default of 5 to 100 with no effect (the manual sugested it wouldn't).

Alex

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 27 1999 - 05:48:51 CDT