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

From: Antony Raj <ca_raj_at_yahoo.com>
Date: Fri, 10 Feb 2012 12:21:54 -0800 (PST)
Message-ID: <1328905314.33818.YahooMailNeo_at_web36805.mail.mud.yahoo.com>

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 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

DIT_FLAG             3
 
 

Thanks
    

---

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 10 2012 - 14:21:54 CST