Re: Concatenated Index Column Order - Does it really matters?
- From: Antony Raj <ca_raj@xxxxxxxxx>
- To: "oratune@xxxxxxxxx" <oratune@xxxxxxxxx>, "mark.powell2@xxxxxx" <mark.powell2@xxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 10 Feb 2012 13:17:38 -0800 (PST)
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@xxxxxxxxx>
To: "ca_raj@xxxxxxxxx" <ca_raj@xxxxxxxxx>; "mark.powell2@xxxxxx"
<mark.powell2@xxxxxx>; "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx>
To: "mark.powell2@xxxxxx" <mark.powell2@xxxxxx>; "oracle-l@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
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@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
basis.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
