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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461