Re: Index build order

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 27 Jan 2009 13:10:43 +0000
Message-ID: <7765c8970901270510p7b8b07aei867e1d84faf9c2a_at_mail.gmail.com>

Jonathan Lewis has a nice demo of this: if you can arrange things so that an indexed access path is costed the same using the cbo then Oracle will pick the index with the first name alphabetically. In the demo case of course BAD_INDEX is chosen over GOOD_INDEX. Renaming it to TERRIBLE_INDEX will 'solve'' the issue. This case is much more likely when setting OPTIMIZER_INDEXxxxx parameters to round costs of index access downwards.

Niall

On Tue, Jan 27, 2009 at 9:02 AM, Nigel Thomas < nigel.cl.thomas_at_googlemail.com> wrote:

> Martin
>
> It was the case in Oracle v5 / v6 (and for all I know may still be) that
> the rule-based optimizer chose between possible indexes (all other things
> being equal) on the basis of creation date (or at least, the sequence in the
> IND$ table) - probably because that affected the position of the index
> definition in memory. Similarly, the order of visiting tables in an
> execution path tended to be reverse lexical (in the last resort).
>
> There must still be cases where "all other things (such as statistics) are
> equal" and that there is some deterministic fallback mechanism for any
> optimizer to decide what to do - such as picking the first object in the
> dictionary cache. Depending on the mechanism, this may seem to support
> observations such as this one.
>
> Regards Nigel
>
> 2009/1/27 Martin Klier <Martin.Klier_at_klug-is.de>
>
>> does the index build order matter in any way? There's rumor that the order
>> in which indexes are built does affect query performance.
>>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 27 2009 - 07:10:43 CST