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

Posted in: Technical Track

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.


Interested in working with Christo? Schedule a tech call.

About the Author

An Oracle ACE with a deep understanding of databases, application memory, and input/output interactions, Christo is an expert at optimizing the performance of the most complex infrastructures. Methodical and efficiency-oriented, he equates the role of an ATCG Principal Consultant in many ways to that of a data analyst: both require a rigorous sifting-through of information to identify solutions to often large and complex problems. A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

3 Comments. Leave new

Oracle should let “sequencename.nextval” as default column value

deepak choubey
February 3, 2013 1:01 pm

is there any specific reason why the default constraint is used before not null constraint?


Very informative Article.There is so much information about the Oracle in this post. keep it up and thanks for the great content. Bookmarked your site.
oracle certification courses


Leave a Reply

Your email address will not be published. Required fields are marked *