List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:June 17 2011 4:02pm
Subject:RE: optimization strategies based on file-level storage
View as plain text  

On Fri, June 17, 2011 07:11, Jerry Schwartz wrote:
>>-----Original Message-----
> <snip>
> 
>>What I was really trying to figure out was why it takes me 4
hours to
>>add a new column to my 22-million-row table, and
whether a different
>>table design can avoid that problem. 
That reply in the forum says,
>>"ALTER TABLE ... ADD
COLUMN will always copy the entire table over,
>>and rebuild
all the indexes. (And, effectively, do an OPTIMIZE.) Do
>>_not_
leave space for extra columns, it won't help."  I'm about to
>>reply and point out the trick that you suggested to me: create
dummy
>>columns early and then just rename them later :)
>>
>>         -Bennett
>>
> [JS]
They will be too small, or the wrong type, or there won't be enough
> of
> them. Based upon 30+ years of database design, I'd bet
money on it. ;-)
> 
> Regards,
> 
> Jerry
Schwartz
> Global Information Incorporated
The only
"alternative design" would be to create another table with the
added columns and a common key field and then lock the primary table and
populate it with the keys from the original table, and I'm not convinced
that would be any faster or less disruptive.
------
William R.
Mussatto
Systems Engineer
http://www.csz.com
909-920-9154


Thread
optimization strategies based on file-level storageBennett Haselton14 Jun
RE: optimization strategies based on file-level storageBennett Haselton14 Jun
  • Re: optimization strategies based on file-level storageJohan De Meersman14 Jun
    • Re: optimization strategies based on file-level storageBennett Haselton15 Jun
      • Re: optimization strategies based on file-level storageJohan De Meersman17 Jun
        • Re: optimization strategies based on file-level storageBennett Haselton17 Jun
          • RE: optimization strategies based on file-level storageJerry Schwartz17 Jun
            • RE: optimization strategies based on file-level storageWm Mussatto17 Jun