Re: Concatenated Index Column Order - Does it really matters?
Date: Fri, 10 Feb 2012 13:17:38 -0800 (PST)
Message-ID: <1328908658.56651.YahooMailNeo_at_web36803.mail.mud.yahoo.com>
Hi David,
I agree with you.It's all depends on the data distribution. But in general,is it recommended to index a column which is getting updated even though it's used as a predicate in a query? My understanding is that when a column is updated,Oracle has to to delete an entry and make a new index entry and if we update quite a larger number of rows,then index maintenance would slow down the elapsed time of UPDATE.
Thanks
From: David Fitzjarrell <oratune_at_yahoo.com> To: "ca_raj_at_yahoo.com" <ca_raj_at_yahoo.com>; "mark.powell2_at_hp.com" <mark.powell2_at_hp.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Friday, February 10, 2012 4:02 PM
Subject: Re: Concatenated Index Column Order - Does it really matters?
I agree with Mark that the most selective column should be the leading column, however it does depend upon your data distribution and how the index will be used (as in which columns will be most often accessed). Oracle can occasionally invoke an index skip scan although I usually find this to occur when the leading column is not the most selective. This also depends upon which release of Oracle you're using; with 11g you could test this by creating the index as invisible then activating it at the session level to determine how the suite of queries utilize it. Tweak the index as necessary then create the tested and approved configuration in production.
I'll state again your situation depends upon how your data in the various columns is distributed. There is no 'one-shot-works-in-every-situation' recommendation.
David Fitzjarrell
From: Antony Raj <ca_raj_at_yahoo.com>
To: "mark.powell2_at_hp.com" <mark.powell2_at_hp.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Friday, February 10, 2012 1:21 PM
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 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 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 10 2012 - 15:17:38 CST