Re: Concatenated Index Column Order - Does it really matters?
Date: Mon, 13 Feb 2012 10:28:45 -0800 (PST)
Message-ID: <1329157725.73903.YahooMailNeo_at_web36804.mail.mud.yahoo.com>
Thank you Wolfgang!
I am 100% with your comment.
From: Wolfgang Breitling <breitliw_at_centrexcc.com> To: Mark.Bobak_at_proquest.com
Cc: "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: Sunday, February 12, 2012 8:27 PM
Subject: Re: Concatenated Index Column Order - Does it really matters?
I beg to differ a bit. Column volatility should factor into the decision.
I did encounter an extreme example a few years back. There was an update statement in a job which took several hours. When we looked into trying to tune it, the statement ran in a few seconds when we re-ran it. Digging into it I found that the faster update wasn't really a re-run of the orignal update. In the job stream the table was truncated and then re-loaded with some of the columns being initially assigned default values which would be updated to final values ( based on content from other tables ) later in the jobstream. On of the columns was initialized to 0 and later updated to huge values and one of the indexes had this column as the leading column, correct for the later use of the table. The update of the leading column had extreme index maintenance implication. Every index entry needed to be removed from the "left" edge of the index and inserted into the "right". That caused the runtime of several hours. When we "re-ran" the update the values
for this column didn't
change so there was no index maintenance. Altering the index unusable prior to the update statement ( not prior to the job since a truncate makes indexes usable ) made the update run several hours faster.
You need to weight the benefit of an index in queries against the maintenance cost in dml and column order and volatility play an important role there.
On 2012-02-10, at 3:02 PM, Bobak, Mark wrote:
> Column order in a concatenated index certainly does matter. But, column volatility shouldn't be a consideration.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 13 2012 - 12:28:45 CST