As an application is updated to a new version, it’s quite often that schema could change with additional columns on existing tables.

From Oracle 11g, there is a new feature when adding a new column to a table:

“When you add a column, the initial value of each row for the new column is null. If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.

This optimized behavior differs from earlier releases, when as part of the ALTER TABLE operation Oracle Database updated each row in the newly created column with the default value, and then fired any AFTER UPDATE triggers defined on the table. However, the optimized behavior is subject to the following restrictions:”

• The table cannot have any LOB columns. It cannot be index-organized, temporary, or part of a cluster. It also cannot be a queue table, an object table, or the container table of a materialized view.
• The column being added cannot be encrypted, and cannot be an object column, nested table column, or a LOB column.

You cannot add a column with a NOT NULL constraint if the table has any rows unless you also specify the DEFAULT clause. With this new feature, the statement “alter table” as below to add a new column on a huge table with NOT NULL and DEFAULT will return right away:

13:36:13 SQL> select count(*) from jli_test_0112;

  COUNT(*)
----------
 233191486

Elapsed: 00:03:56.84

21:51:46 SQL> ALTER TABLE jli_test_0112 ADD code VARCHAR2(10) default '00000' not null;

Table altered.

Elapsed: 00:00:00.04

which would take a long time before 11g because Oracle would update all rows with the default value which could generate lots of undo, redo — significant overhead, plus exclusive DDL lock needed.

Nice feature, huh?

However, this new feature does have a limitation not mentioned in the guide — when default value is a dynamic one like below using SYSTIMESTAMP, Oracle will still update all existing rows:

SQL> ALTER TABLE jli_test_0112 ADD UPDATED_TIME TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL;

Table altered.

Elapsed: 01:07:45.19

It’s unaovidable to update all rows when using a dynamic value as the DEFAULT value. However this can be mitigated by adding the column first, then modify it with the DEFAULT clause.

ALTER TABLE jli_test_0112 ADD UPDATED_TIME TIMESTAMP (6) WITH LOCAL TIME ZONE;


ALTER TABLE jli_test_0112 MODIFY UPDATED_TIME DEFAULT SYSTIMESTAMP NOT NULL NOVALIDATE;

Finally update the new column using other techniques such as CATS, updating in small batch, parallel DML update SQL, DBMS_PARALLEL_EXECUTE, online table redefinition with DBMS_REDEFINITION all of which will be affected by the redo/undo overhead or even row migration.

• Oracle