Re: Optimizer depth - Index usage dependant on name of index

In article <7v6lai$mer$1_at_nnrp1.deja.com>,   alexcosser_at_my-deja.com wrote:
> 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
>

I hope you re-analyzed the table each time you dropped/added the indexes. But you can make the sub-query use the desired index by coding a hint in the sub-query. Each sub-query in an SQL statment can have its own hint. We have noticed a tendency of the optimizer to switch to using a just rebult index in the case of two indexes having the same starting columns and have had to code a couple of index hints to stop it from doing that.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 27 1999 - 15:21:35 CDT