Re: best practice in upgrading db structure

On Mar 28, 2006, at 8:40 PM, Robert Treat wrote:
>> Depends on how much data you need to modify. For small tables, I  
>> stick
>> with ALTER TABLE because it's a lot cleaner/easier. For larger  
>> tables,
>> you might want to CREATE TABLE AS SELECT ..., or maybe copy out  
>> and copy
>> back in.
>
> This seems backwards to me. On larger tables I tend to favor alter  
> table for
> adding/dropping columns since the table doesn't need to be  
> rewritten, and on
> smaller tables I'd be more likely to use CTAS (although even then  
> still
> pretty unlikely)

It depends on what exactly you're doing. For example, if you're  
adding a new field and have to populate it with data, you end up  
rewriting the entire table, but in a way that leads to considerable  
bloat. Of course if you can get away without re-writing the entire  
table you absolutely want to go that route.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461