Re: performance and downtime: adding columns with a not null default

You can add the columns as nullable and write multiple jobs to update certain portions of the table in parallel.

But with all those new columns - one would imagine that row migration could bite hard, which would mean a unload/reload anyway.

It may be best to do the entire exercise offline with a create-table-as-select

hth
connor

"Greg Caulton" <gcaulton_at_nygh.on.ca> wrote in message news:493716b4.0111261813.27ca47b1_at_posting.google.com...
> Hi,
> I have a 2GB table (1 million rows) with which I need to add a dozen
> or so columns and set perhaps two dozen defaults values with not nulls
> (not my design...)
>
> Offpeak it has new rows added every minute or two and so I can't
> afford to exclusively lock the table for a long time.
> I could work at one column at a time while the database is in use.
> Or I could pay the piper and make the changes during downtime - doing
> all the columns at once.
>
> I will work on estimating the time for those two options more
> precisely,
> but what are peoples experience as to the most optimal way of doing
> this?
>
> thanks
>
> Greg
Received on Tue Nov 27 2001 - 15:36:43 CST