One of the new features of ORACLE 11G (add fields with default)

Before 11g, add a field with default value, the implementation principle is as follows:

ALTER TABLE T1 add C1 varchar2 (a) default ' XX ' NOT null;

Assuming that the T1 table has 40 million rows of data, Oracle returns a "Success Prompt" to the user when it executes the above DDL statement while updating the 40 million rows of data successfully. The following issues may be caused:

1. Updating 40 million rows of data takes a long time to complete.

2. Exhaustion of all undo table space, and generate a large number of redo logs, and even lead to full disk space, standby library delay.

3. Large amount of database, system performance overhead.

After 11g (including), the implementation principle is as follows:

The above DDL operations do not update records for the entire table. Does the C1 field return a null value when the user queries the existing data that contains the new field (C1)? Of course not, actually, when the user queries the existing data in the C1 field,

Oracle obtains the default value of the field from the data dictionary and returns it to the user.

Therefore, when a new field with default value and non-null is added to the Oracle database after 11g, only the data dictionary information for the corresponding table is updated, so it is not necessary to care about the effects of undo and redo.

Reference: http://www.oracle.com/technetwork/articles/sql/11g-schemamanagement-089869.html

One of the new features of ORACLE 11G (add fields with default)