Adding column with default value on a large table...

We all know that we can easily add a column to an existing table with DEFAULT value. It's not a problem when the the table size is small because when adding a column with default value, all existing rows needs to be updated with the value specified in default clause.

Problem is only when the table has millions of records. It will take anywhere from minutes to close to hours (depending on the size of the table).

Thanks to 11g with its new feature:

Alter Script:

alter table table_name add column_name varchar2(3) default 'YES' NOT NULL;

The above statement will not issue an update to existing records. New records will have the value YES. So, what will happen to existing records data for this column? Here comes the 11g new feature, when a user selects the column for an existing record, oracle gets the default value from the data dictionary.

Now you can happily add a column with default value to even billion records table in fraction of a second!!! sweet...

Here is the test results with and without NOT NULL:

15:59:35 SQL> select count(1)from ln_stage_data;

  COUNT(1)
----------
  12540411

Elapsed: 00:00:00.93
15:59:40 SQL> alter table ln_stage_data add test_flag char(1) default 'Y';

Table altered.

Elapsed: 01:02:03.38
17:01:51 SQL> alter table ln_stage_data add test_flag2 char(1) default 'Y' not null;

Table altered.

Elapsed: 00:00:01.13
17:02:14 SQL>