RE: Index build order
Date: Tue, 27 Jan 2009 09:36:57 -0500
When all else being equal occurs and the CBO makes the choice based on the first alphabetical index name is a recent change, probably with 10g. On older versions the highest object_id (most recently rebuild or created) index was chosen. We had fun with than feature where the CBO/RULE both had a choice of two single column indexes to use. By dropping and re-creating one of the indexes that index became the choice.
- Mark D Powell --
Phone (313) 592-5148
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield
Sent: Tuesday, January 27, 2009 8:11 AM To: nigel.cl.thomas_at_googlemail.com Cc: Martin.Klier_at_klug-is.de; ORACLE-L Subject: Re: Index build order Jonathan Lewis has a nice demo of this: if you can arrangethings 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.
On Tue, Jan 27, 2009 at 9:02 AM, Nigel Thomas <nigel.cl.thomas_at_googlemail.com> wrote:
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.
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.infoReceived on Tue Jan 27 2009 - 08:36:57 CST