Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Introduction:
Prior to Oracle database 11g, adding a new column with a default NOT NULL value were a challenge. Especially, when the concerned table contains thousands or millions of records. Oracle 10g updates ALL the rows of the concerned table with the new default value. In my busy real time OLTP databases, I had to create another intermediate table with the same structure plus the new default not null column. This solution needs a switch to the second table and a rename table operation.
As you can Imagine, this solution takes time, resources, space, loads, locks.
Oracle database 11g offers a new quick, easy, instant solution; instead of updating ALL the rows of the table, Oracle 11g now updates the metadata only.

The process of adding the new column with a default NOT NULL value is now much more easy, and faster.
In the following section, we will compare this process between an Oracle 10g release 2 database and an Oracle 11g release 2 database.

Adding the new column with a default NOT NULL in 10g
In this section, we will create 2 different sessions (session1 and session2).

SESSION1:

C:\Documents and Settings\Wiss>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mar Mar 8 10:44:28 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect sys as sysdba

Introduzca la contrase±a:

Conectado.

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> create table sales as

  2  select rownum as id,

  3  mod(rownum,5) as product_id,

  4  mod(rownum,100) as client_id,

  5  2000 as price,

  6  to_date(

  7  '01.'||lpad(to_char(mod(rownum,3)+1),2,'0')||'.2011','dd.mm.yyyy')

  8  as c_date

  9  from dual

 10  connect by level <= 2.5e5;

Tabla creada.

SQL> set timi on

SQL> select count(*) from sales;

  COUNT(*)

----------

    250000

Transcurrido: 00:00:00.70

SQL>

SESSION2:

C:\Documents and Settings\Wiss>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mar Mar 8 11:00:59 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect sys as sysdba

Introduzca la contrase±a:

Conectado.

SQL> set timi on

SQL> desc sales;

 Nombre                                    +Nulo?   Tipo

 ----------------------------------------- -------- -------------------------

 ID                                                 NUMBER

 PRODUCT_ID                                         NUMBER

 CLIENT_ID                                          NUMBER

 PRICE                                              NUMBER

 C_DATE                                             DATE

SQL> select count(*) from sales;

  COUNT(*)

----------

    250000

Transcurrido: 00:00:00.04

In the session1 we set the event 10046 to analyse deeply the process of adding a column with default NOT NULL value in sales table:

SQL> alter session set events '10046 trace name context forever,

  2  level 16';

SesiŸn modificada.

Transcurrido: 00:00:00.00

SQL> alter table sales add (provider_id NUMBER(2) default '0' NOT NULL);

Tabla modificada.

Transcurrido: 00:01:09.42

SQL> alter session set events '10046 trace name context off';

SesiŸn modificada.

Transcurrido: 00:00:00.00

SQL>

During the execution of the alter table command, I issued the following command in the session2. The simple count all the rows of the table now takes 01min:06s.54ms, when it first took only 4ms. This is because the table was locked for update all the rows of sales table.
Also note that alter table command took 00:01min:09s.42ms.

--During alter table command

SQL> select count(*) from sales;

  COUNT(*)

----------

    250000

Transcurrido: 00:01:06.54

SQL>

We analyse the trace file generated in the USER_DUMP_DEST directory, we see these lines: (with UPDATE SALES operation)

=====================

PARSING IN CURSOR #5 len=37 dep=1 uid=0 oct=6 lid=0 tim=5763158610 hv=3800351085 ad='2091018c'

update "SALES" set "PROVIDER_ID"='0' 

END OF STMT

PARSE #5:c=0,e=1101,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=5763158602

*** 2011-03-08 11:18:25.218

EXEC #5:c=30046875,e=31481436,p=925,cr=930,cu=254807,mis=0,r=250000,dep=1,og=4,tim=5794641561

STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  SALES (cr=930 pr=925 pw=0 time=31481367 us)'

STAT #5 id=2 cnt=250000 pid=1 pos=1 obj=52550 op='TABLE ACCESS FULL SALES (cr=930 pr=925 pw=0 time=1294122 us)'

=====================

Adding the new column with a default NOT NULL in 11g
In this section too, we will create 2 different sessions (session1 and session2).

SESSION 1

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> ---session1
SQL> create table sales as
  2  select rownum as id,
  3  mod(rownum,5) as product_id,
  4  mod(rownum,100) as client_id,
  5  2000 as price,
  6  to_date(
  7  '01.'||lpad(to_char(mod(rownum,3)+1),2,'0')||'.2011','dd.mm.yyyy')
  8  as c_date
  9  from dual
 10  connect by level <= 2.5e5;

Table created.

SQL> set timi on
SQL> select count(*) from sales;

  COUNT(*)
----------
    250000

Elapsed: 00:00:00.04
SQL> alter session set events '10046 trace name context forever,
  2  level 16';

Session altered.

Elapsed: 00:00:00.02
SQL> alter table sales add (provider_id NUMBER(2) default '0' NOT NULL);

Table altered.

Elapsed: 00:00:00.21
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00

SESSION2

SQL> ---session2
SQL> set timi on
SQL> --- Before issue the Alter table statement
SQL> select count(*) from sales;

  COUNT(*)
----------
    250000

Elapsed: 00:00:00.02
SQL> --During alter table command
SQL> select count(*) from sales;

  COUNT(*)
----------
    250000

Elapsed: 00:00:00.02
SQL>

From above, note that the alter table statement is much more faster in 11g than in 10g.
It only takes 21ms in my 11g case, whereas it took more than 1min in my 10g case.
Also, note that during the alter table operation, the session2 has the resources available to query the table.
When, we analyse the trace file in USER_DUMP_DEST (in 11g diagnostic_dest parameter), we can see there is NO more an “UPDATE SALES” statement in the trace file. Thus, because now during the add column operation Oracle stores the default value as a metadata, no more updates in the rows.