RE: Index build order

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 27 Jan 2009 11:35:05 -0500
Message-ID: <C73AEAD7A4F24F39A3C9852B84D8DE82_at_rsiz.com>

Slight clarification: alter index rebuild avoids the object_id change. As stated, drop/re-creating gets a new object_id.  

I've always been slightly annoyed that the tie-breakers are side effects rather than an explicit dictionary attribute of indexes, in place of selectively hinting which index to use. The default value could be the index name in order to be stable relative to current functionality, but not force you to "mis-name" your indexes to control the behavior. Of course that also leaves out overriding which index to use only in the case of a tie, so, for example, histograms might be the cost based differentiation with some range parameters one way or the other, but in some ranges there could be a tie. When the effective size per row of two indexes is the same a tie is not that as unlikely an event as might be expected. That could be an interesting hint to have in your arsenal - listing two indexes in a way that asked the CBO to choose the cheaper, but told it which to prefer in case of a tie (as an override to the putative explicit or current name based default tie breaker).  

Hmm - maybe I'll post this on mix along with explicit statement of rows per block for tables on creation or by alter (which Julian Dyke and I agree is also annoying to have to be done with by a hack after stuffing one or more rows in the table). Both of these enhancements would of course merely make explicit and simple things you can already do in silly round about ways that seem to change over time with or without clear announcement.  

mwf  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
Sent: Tuesday, January 27, 2009 9:37 AM
To: ORACLE-L
Subject: RE: Index build order    

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_at_freelists.org [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 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 - 10:35:05 CST