Re: adding a column with default value on a very large table

In article <a6qnvs$hlo$1_at_s1.read.news.oleane.net>, "Frédéric says...
>
>Hi !
>
>I want to add a new column with a default value on a very large table (100
>million of records).
>I would like to know if using the following SQL command is suited, seen
>under the angle of the very large size :
>
>alter table XXXX add column YYYY varchar(4) default '0000';
>
>The good point is that this doesn't use the rollback segments, so the
>problem is not here.
>

why do you (mistakenly) believe that won't use rollback? That statement needs to maintain the integrity of the table. That'll generate rollback and redo for sure.

If the machine crashed halfway through, we need to rollback upon restart. It will certainly generate rollback.

>1) The question is about the speed performances. Is this the speedest method
>?
>

updating 1,000,000 rows will take a bit. What *may* be faster is:

create table new_table as select a.*, '0000' y from old_table

and use parallel/unrecoverable as well. then

index (unrecoverable in parallel) new table add constraints...

drop table old_table
rename new_table to old_table

>2) Besides I want to know how to estimate the time necessary for this
>operation.

start with a small sample table, run the command, time it. double the size of the sample, run the command, time it. double the size of the doubled example and do it again. plot points and see if extrapolation works...

impossible to say given that the size of the machine, concurrent activity, the amount of ram, the type of disks, etc etc etc will all affect that.

>
>Thank you,
>Frédéric Bachelier
>
>
>
>
>

--
Thomas Kyte (tkyte@us.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