Adding new mandatory columns with specified default 11g vs. 10g

DATA ARCHITECTURE

by Ludek Bob Jankovsky, 28-Feb-2009 (ORACLE STUFFS)

I am sure you have met the issue sometimes. You are adding new mandatory column into very large table. Oracle starts update all rows with default value of the column - like it would never finish. It takes huge amount of time in the data definition phase of migration scripts - not much agreeable issue. Lets see how it has been solved in the 10g (the same as 9i) version of Oracle and what is different in Oracle 11g.

Oracle 10g
Solution in Oracle 10g (9i) supposes 3 steps:

Alter table ... add column ... not null with default definition in a DDL phase of migration.
Update added columns to default values explicitly in a data migration phase.
Alter table ... modify column ... NOT NULL NOVALIDATE

Oracle 11g
Solution in Oracle 11g is much easier. Oracle 11g does not update added mandatory columns physically, despite it returns default values when undefined value is found. You can just straight alter table ... add column default ... not null, and it works quickly even on very large tables. It works even if some partitions are in read-only tablespaces.

Lets see an example of ten million records table:

SQL> select count(0) from tt8; COUNT(0) ---------- 14651589

Now we will add a new mandatory column by the standard way:

alter table tt8 add NEWCOL varchar2(30) default 'XNA' not null;

It toke about 1 sec - obviously it did not update table physically.

SQL> select distinct newcol from tt8; NEWCOL ------------------------------ XNA

... and values look like updated.

Sounds like fairy-tail, Oracle substitutes physically undefined values with a default, anyway what if I change the default:

alter table tt8 modify NEWCOL default 'N/A';

And Oracle works standardly. First default value remains the same like the 10g behavior.

SQL> select distinct newcol from tt8; NEWCOL ------------------------------ XNA

Now we try to make the NEWCOL column optional:

alter table tt8 modify NEWCOL null;

... and the first surprise: It took several (24) minutes!!!
That is the price payed for the functionality. It helps us to add mandatory columns but be careful when you change them to optional. It is the place where oracle compensates consistency of changes and when data should be modified physically.
At least - the result is consistent:)

SQL> select distinct newcol from tt8; NEWCOL ------------------------------ XNA

Ludek Bob Jankovsky