Adding Columns with Default Values and Not Null in Oracle 11g

Oracle 11g has a new performance enhancement when adding columns. In the pre-11g releases, adding a new not null column with a default value would have caused a massive update on the entire table, locking it for the operation and generating tons of undo and redo. I’ve seen this happening in production.

Oracle 11g has improved this behaviour by storing the default value in the metadata and making the column addition instantaneous. An example of this feature at work can be seen in 11g Enhancement for ALTER TABLE .. ADD COLUMN Functionality and some bugs regarding sysdate, as pointed out in the comments.

Although this is a welcomed enhancement, there are some unexpected aspects beyond the basic operations.

First, we know default values for new columns are stored in the metadata, but what happens when you change the default?

create table mytest (a number); -- instant
insert into mytest (a) select rownum from dba_objects; -- 10 seconds
alter table mytest add b varchar2(2000) default rpad('x',1000,'x') not null; --instant

And now the test:

alter table mytest modify b  default rpad('Z',1000,'Z') ;-- instant !

Instant! If we query the table what are we going to see? The correct, ‘xxxxx’ value or the new ‘ZZZZZ’ value? How does it work?

Obviously Oracle has to behave properly. So querying the table shows correctly the already-initialized “xxxx” value. Through experimentation, I have determined that this enhancement works as follows:

There are two default values for each column:

• one default expression for “not null with default value” columns that do not have a value at the block level
• another default value for all new inserted records

I’ll spare you all the test cases, but based on the above mechanics, we can observe the following not-so-obvious behaviours:

• Rebuilding the table with “alter table move” can (and probably will) make it bigger, as all the defaults are initialized at the block level.
• Updating other columns will not initialize the default value, thus does not grow the table.
• Changing the column constraint to “null” (from not null) will initialize the default values, causing a massive update and locking of the table
• Some “magic” can be applied to large fact tables by adding, instead of creating them with default values for very popular values, but that can be a nightmare to maintain.

Virtual columns may present an innovative space-saving approach by using nvl for default values. Of course, it works differently, but for very large tables, it may be of significant advantage. For example:

create table mytest ( colA_data varchar2(30), colA as (nvl(colA_data, 'DEFAULT VALUE')));

The default value will not consume any space (except 1 byte for ‘null’ value), ever.