Simple blank matters

I saw this interesting bug on Metalink headlines and I thought it will be nice to post it here.

This is the heading of the bug ‘ADDING COLUMN WITH DEFAULT NULL LEADS TO UNNECESSARY FULL TABLE UPDATE ‘ numbered 8840491.

Normally, if you add a column with a default null value, there won’t be an update on the table itself (I think after 10G because trace for 9i shows update on table), running at the background, but if you put some spaces to make your alter statement look better, it is where the problem begins.

What I mean is these 4 statements are different during runtime due to the bug.

alter table test add (id number default null);

alter table test add ( id number default null);

alter table test add (id number default null );

alter table test add ( id number default null );

Lets see how do they differ.  Actual bug is reported on 10.2.0.4  and I am able reproduce it on 11.1.0.6

SQL> set timing on
SQL> create table test (id number not null);

Table created.

Elapsed: 00:00:00.54
SQL> insert into test
  2  select rownum from dual connect by level<=1000000;

1000000 rows created.

Elapsed: 00:00:01.65

SQL> alter session set tracefile_identifier=null_bug_test;

Session altered.

Elapsed: 00:00:00.00
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
SQL> --no space before after ()
SQL> alter table test add (id2 number default null)  ;

Table altered.

Elapsed: 00:00:00.09
SQL> --space after (
SQL> alter table test add ( id3 number default null)  ;

Table altered.

Elapsed: 00:00:00.01
SQL> --space before )
SQL> alter table test add (id4 number default null )  ;

Table altered.

Elapsed: 00:01:12.90
SQL> --space before and after ()
SQL> alter table test add ( id5 number default null ) ;

Table altered.

Elapsed: 00:01:34.27
SQL> -- spaces everywhere but not after before ()
SQL> alter table test add (id6  number   default   null)  ;

Table altered.

Elapsed: 00:00:00.04
SQL>

As you see from the timings some of them are longer than expected. Lets find them in tracefile

These are the update statements from the trace file.

=====================
PARSING IN CURSOR #1 len=29 dep=1 uid=82 oct=6 lid=82 tim=21689660840 hv=720540867 ad='30679e28' sqlid='7nb3cf4pg5563'
update "TEST" set "ID4"=null 
END OF STMT
PARSE #1:c=15625,e=17207,p=0,cr=106,cu=1,mis=1,r=0,dep=1,og=1,tim=21689660835
=====================
PARSING IN CURSOR #5 len=29 dep=1 uid=82 oct=6 lid=82 tim=21762749484 hv=1088193227 ad='30679058' sqlid='f4g28hd0dt0qb'
update "TEST" set "ID5"=null 
END OF STMT
PARSE #5:c=46875,e=146504,p=294,cr=100,cu=0,mis=1,r=0,dep=1,og=1,tim=21762749480

As you see space before ) and space before and after () causing full table update and guess the damage of this tiny hidden change on over 10 million row huge table.

Next time, if you wait your not null new column getting added longer than it needs on 10G>, check the syntax you might be hitting this bug.