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.

Advertisements

About Taral
I am Rookie to Oracle Technology so let's see where it goes

4 Responses to Adding Column In 11g With Default Values

  1. Anand says:

    Thanks for sharing 🙂

  2. Ram says:

    Thank You for sharing.

  3. bismark says:

    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 MOD

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: