Oracle 10g Alter table with Set default value 1

[nlsnco]

Can anyone help on this.
I am new to Oracle and I learned that Oracle is going to every rows in the table when a new column with default value is added via alter table command.
And if we have over 60 Million records, it will take forever to run the simple alter table command. We have done this using DDL.

Does anyone has any suggestion on the best way to do the alter table with default value that will make oracle only apply the default value on any new rows without having to go to every rows in the table?
Or any solution to implement quickly when adding the column with default

Thanks,
Nelson

 

[SantaMufasa]

NLSNCO,

The only way that I can think of to discriminate against existing rows (i.e., apply a particular value to only new rows) is via an ON INSERT trigger. (If you need help with how to code such a trigger, let us know, giving also the name of the table, the column name, and the default value you want in that column when INSERTing.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[Dagon]

Why don't you just do it in two separate steps ?

a) create the new column without the default value
b) add the default value (which doesn't back-populate the existing data).

  1* alter table def_value add (d number)
SQL> /

Table altered.

SQL> select * from def_value;

         A B                    C                  D
---------- -------------------- --------- ----------
         1 xxx
         5 yyy
         9
         1 mmm                  29-JUL-08

SQL> alter table def_value modify (d default 27);

Table altered.

SQL> select * from def_value;

         A B                    C                  D
---------- -------------------- --------- ----------
         1 xxx
         5 yyy
         9
         1 mmm                  29-JUL-08

SQL> insert into def_value (a, b,c) values (97, 'iiii', sysdate);

1 row created.

SQL> select * from def_value;

         A B                    C                  D
---------- -------------------- --------- ----------
         1 xxx
         5 yyy
         9
         1 mmm                  29-JUL-08
        97 iiii                 29-JUL-08         27

SQL>

 

[SantaMufasa]

Why not, indeed ! Excellent suggestion, Dagon. (WDITOT - Why Didn't I Think Of That). Please hava

for your clear thinking.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[jimirvine]

I think that the following is only valid on 10g R1 or higher, but just in case:

drop table a purge;
create table a (a number);
insert into a select level from dual connect by level <= 10;
SELECT * FROM A;
alter table a add b number;
SELECT * FROM A;
alter table a modify  b default 2;
SELECT * FROM A;
insert into a (a) values (20);
SELECT * FROM A;

 

[Dagon]

Well, nlsco does say he is on 10g, although this is posted in the 9i forum.

 

[jimirvine]

Well, nlsco does say he is on 10g

jeez I can be dumb sometimes. (Sorry for basically repeating the code that you supplied 10 minutes before me Dagon. somehow I managed to miss that)