Re: best practice in upgrading db structure

[Jim Nasby <jnasby(at)pervasive(dot)com>]

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