Adding Column In 11g With Default Values

Few days back friend ask me in 10g or previous version adding column in existing table with default values with not null constraint takes longer time while in 11g it takes few seconds.

Well, Answer to this is to test. Starting with 10.2.0.5 then 11g

10.2.0.5
=============

create table test as select rownum col1, 'XX' col2 from dual connect by level <= 20000;

Table created.

Elapsed: 00:00:00.09
alter table test add (col3 varchar2(10) default 'YYYY' not null);

Table altered.

Elapsed: 00:00:01.31

11.2.0.3
=============

create table test as select rownum col1, 'XX' col2 from dual connect by level <= 20000;

Table created.

Elapsed: 00:00:00.14
alter table test add (col3 varchar2(10) default 'YYYY' not null);

Table altered.

Elapsed: 00:00:00.01

Well, it’s a good amount of difference. Reason behind this is before 11g when you add column with default values and not null constraint it has to update all the columns in that tables which are null.

Starting from 11g this has changed. In 11g oracle keeps the information in the dictionary table ecol$. So, when you add column there are just few data dictonary operations. And when you select from that table it find that information from that table to populate values for existing data. But, when you insert new record it insert it with that values as default. It keeps track of that. Also, while doing this operation it takes “ROW EXCLUSIVE MODE NOWAIT” Lock.

Here is the example.

drop table test purge;
create table test pctfree 99 as select rownum col1, 'XX' col2 from dual connect by level <= 10;

tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 0a
col  1: [ 2]  58 58
tab 0, row 3, @0x1f5c
tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 0b
col  1: [ 2]  58 58

select * from test;

      COL1 CO COL3
---------- -- ----------
         1 XX YYYY
         2 XX YYYY
         3 XX YYYY
         4 XX YYYY
         5 XX YYYY
         6 XX YYYY
         7 XX YYYY
         8 XX YYYY
         9 XX YYYY
        10 XX YYYY

select utl_raw.cast_to_varchar2(dbms_lob.substr(BINARYDEFVAL)) from sys.ecol$;

UTL_RAW
---------
YYYY

Now, when you insert a row.

insert into test values(11,'XX','AAAA');

tl: 14 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 0c
col  1: [ 2]  58 58
col  2: [ 4]  41 41 41 41

Also, do disable this feature you can set _add_col_optim_enabled to false and you are back to 10g like behaviour.