Re: Concatenated Index Column Order - Does it really matters?
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Mark.Bobak@xxxxxxxxxxxx
- Date: Sun, 12 Feb 2012 18:27:04 -0700
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
