RE: Index build order

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 27 Jan 2009 09:36:57 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A903643DF2_at_usahm208.amer.corp.eds.com>

 

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 - 08:36:57 CST