Re: Adding column is slow

From: Herman de Boer <h.de.boer_at_itcg.nl>
Date: Thu, 22 Aug 2002 14:01:30 GMT
Message-ID: <ak2qut$5df$1@news1.xs4all.nl>

Peter Laursen wrote:

>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
>
>
>
>

Why not perform a create-table-as-select; followed by a drop and a rename. This runs usually very fast. No rollback, hardly redo (with nologging clause). You only need to have the space for a 2nd large table, and to perform the index rebuild.

Kind Regards,

Herman de Boer
sr consultant
IT Consultancy Group bv. Received on Thu Aug 22 2002 - 09:01:30 CDT