RE: Concatenated Index Column Order - Does it really matters?
Date: Fri, 10 Feb 2012 17:02:06 -0500
(Note, this is a different Mark than replied previously. :-))
Column order in a concatenated index certainly does matter. But, column volatility shouldn't be a consideration. Index query usage, how many queries use the index, and the most popular column in query predicates, should be considered. Secondary to that, all other things being equal, you should put your *least* selective columns first, so that you can leverage index compression.
Hope that helps,
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Antony Raj Sent: Friday, February 10, 2012 3:22 PM
To: mark.powell2_at_hp.com; oracle-l_at_freelists.org Subject: Re: Concatenated Index Column Order - Does it really matters?
Consider the following columns(all of them are used as predicates in different queries) and their distinct values.Can you suggest the order of columns for creating an index? The table has ~ 32million rows.This table is also updated with the column DIT_FLAG. Can we keep a volatile column as a leading column even it's most selective?
Column NUM_DISTINCT PNO 394 EMPLID 31366 DEPBENEF 14 EMPL_RCD 1 PLANTYPE 2 ACTN 3
From: "Powell, Mark" <mark.powell2_at_hp.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Thursday, February 2, 2012 4:57 PM Subject: RE: Concatenated Index Column Order - Does it really matters?
No, you should make the "most selected" column the leading column of the multi-column index. That is if 6 queries reference one or both of the two columns and the 3 queries that reference only one of the columns reference the same column then you normally want that column to be the leading column (providing the data is not badly skewed).
Ever case depends on the data distribution and the actual queries being ran but in general I suggest looking at what columns are referenced and which other indexes you would need to create if you choose a specific order.
The least selective column being first could be beneficial if index compression is used. Again I find use of this feature has to be judged on a case by case basis.Received on Fri Feb 10 2012 - 16:02:06 CST