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

From: Wolfgang Breitling <>
Date: Sun, 12 Feb 2012 18:27:04 -0700
Message-Id: <>

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.

Received on Sun Feb 12 2012 - 19:27:04 CST

Original text of this message