Re: Adding column is slow

In article <3d64c91c$0$43947$edfadb0f_at_dspool01.news.tele.dk>, "Peter says...
>
>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
>
>

You are in effect updating every single row in the table.

I see lots of row migration taking place in your future....

Perhaps instead of alter table add column .... you could:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table new_emp as select * from scott.emp;

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter table new_emp add x number(1) default 0;

Table altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert /*+ append */ into new_emp select emp.*, 0 from scott.emp;

14 rows created.

and then drop the old table and rename the new table to the old. Then, add indexes/constraints/grants/etc to the new table

advantages

o no redo if you use nologging
o no rollback (append hint)
o can be done in parallel using parallel query o no row migration

>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp