Default Column


If you add a column with a default, Oracle dutifully goes back and updates every single existing row. (Which might include a nice trip to row migration heaven!)

Can we avoid this ? Yes, just don't do it "all at once"...

SQL> create table T
  2  as select * from all_objects;

Table created.

SQL> alter table T add NEW_COL1 number default 10;

Table altered.

Elapsed: 00:00:02.08
SQL> alter table T add NEW_COL2 number;

Table altered.

Elapsed: 00:00:00.02
SQL> alter table T modify NEW_COL2 default 10;

Table altered.

Elapsed: 00:00:00.03
SQL> select count(new_col1), count(new_col2)
  2  from T;

COUNT(NEW_COL1) COUNT(NEW_COL2)
--------------- ---------------
          50199               0

Elapsed: 00:00:00.05