New Add Column Functionality - 11g New Features

Prior to Oracle 11g, when you add a column with not null constraint and with a default value, Oracle actually populates the value in all the rows of the table. All the rows, ouch! Imagine a multimillion-row table where the data will be updated several million times and how much redo and undo it will generate. In addition, it will also lock the table for the entire duration preventing DDLs. This caused a lot of consternation among users.But In Oracle 11g, this is handled different.

Ran the script in 9iR2, 10gR2 and 11gR1 and Tkprof show's the below results.

create table T nologging
as
select *
from all_objects;

alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 16';
alter table T add grade varchar2(1) default 'x' not null;

Oracle - 9iR2
********************************************************************************

alter table t add grade varchar2(1) default 'x' not null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.01          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 107 
********************************************************************************

update "T" set "GRADE"='x'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.22          0          0          0           0
Execute      1      1.35       1.95        452        459      33914       33243
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.35       2.18        452        459      33914       33243

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 107     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE 
  33243   TABLE ACCESS FULL T
********************************************************************************

Oracle -10gR2
********************************************************************************

alter table t add grade varchar2(1) default 'x' not null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.04          0          0          0           0
Execute      1      0.01       0.30          0          1          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.35          0          1          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
********************************************************************************

update "T" set "GRADE"='x'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      3.21       5.51        548       2228     304319       56172
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.21       5.51        548       2229     304319       56172

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  T (cr=1598 pr=95 pw=0 time=3176654 us)
 159118   TABLE ACCESS FULL T (cr=2220 pr=548 pw=0 time=1113963 us)
********************************************************************************

Oracle - 11gR1
********************************************************************************

SQL ID : dpj1atuacv8zx
alter table t add grade varchar2(1) default 'x' not null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1         25           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          1         25           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 
********************************************************************************

looking at the 11g Trace File I could not see a reference to the UPDATE T...Statement This behavior results in significantly less redo and undo, and also completes faster.