RE: Index build order
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- 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
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Niall Litchfield
Sent: Tuesday, January 27, 2009 8:11 AM
To: nigel.cl.thomas@xxxxxxxxxxxxxx
Cc: Martin.Klier@xxxxxxxxxx; ORACLE-L
Subject: Re: Index build order
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@xxxxxxxxxxxxxx> 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@xxxxxxxxxx>
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
Other related posts:
