RE: Concatenated Index Column Order - Does it really matters?

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "ca_raj@xxxxxxxxx" <ca_raj@xxxxxxxxx>, "mark.powell2@xxxxxx" <mark.powell2@xxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 10 Feb 2012 17:02:06 -0500

Hi Antony,

(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 

Hope that  helps,


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Antony Raj
Sent: Friday, February 10, 2012 3:22 PM
To: mark.powell2@xxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Concatenated Index Column Order - Does it really matters?

Hi Mark,
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 
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
DIT_FLAG             3

From: "Powell, Mark" <mark.powell2@xxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 
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 


Other related posts: