Re: Adding column is slow

Hi Peter,

It's going to take a while as you are effectively inserting the column and due to the default clause, updating it for every row in the table.

Indexes and constraints will have no effect on this operation as they are not being accessed in any way.

If you have an after update trigger, this would indeed fire (again thanks to the default clause) so disabling such triggers would make things a little faster.

To speed it up, you could always forget about the default clause but then that's not quite what you want. You could also look at running this in parallel.

Cheers

Richard
"Peter Laursen" <ptl_at_edbgruppen.dk> wrote in message news:3d64c91c$0$43947$edfadb0f_at_dspool01.news.tele.dk...
> Hi,
>
> System(s) 8.0.5 and 8.1.7 SE
>
> Adding a new column to a large table is taking hours. Its part of an
update
> that will be run at many customers. The table will contain approx 100M
rows.
> The table has 5 number fields and a date field. A new 'number(1) default
0'
> is beeing added
> Assuming the update will run as the only user connected what could be done
> to speed up things?
> Will any of the following help: Disabling triggers? Disabling constraints?
> Dropping indexes and rebuilding?
>
> Also how do I calculate the how much rollback space is needed?
>
> TIA
> Peter Laursen
>
>
>
>
Received on Thu Aug 22 2002 - 08:59:28 CDT