Mar 102014
 
alter table from_11g add 
  column_with_a varchar2(100) default 
    'is instant provided you set it' not null;

Prior to 11g the above statement could take a very long time if the table was “big”. I was reminded of this by a recent discussion on Oracle-L. Tom Kyte also discusses this in more detail on asktom.

For a quick and dirty demo, keep reading

create table from_11g as
  select rownum x
  from   dual
  connect by level <= 1000000;

table FROM_11G created.
Elapsed: 00:00:01.610

alter table from_11g add 
  column_with_a varchar2(100) default 
    'is instant provided you set it' not null;

table FROM_11G altered.
Elapsed: 00:00:00.078

alter table from_11g 
  set unused column column_with_a;

table FROM_11G altered.
Elapsed: 00:00:00.078

alter table from_11g add 
  column_with_a varchar2(100) default 
    'is instant provided you set it' null;

table FROM_11G altered.
Elapsed: 00:01:37.313

drop table from_11g purge;

table FROM_11G dropped.
Elapsed: 00:00:00.453

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)