Re: Adding column is slow

Peter Laursen wrote:

> 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

Default 0 results in a :

UPDATE your_table
  SET number = 0;

If the table is big - the update will run for hours.. assuming you have enough rollbacks.

> Assuming the update will run as the only user connected what could be done
> to speed up things?

Take away the default. Make it null.

> Will any of the following help: Disabling triggers? Disabling constraints?
> Dropping indexes and rebuilding?

Yes - removing all but the primary key constraint index can significantly increase the performance of a massive update/insert on a large table.

--
Billy