Adding columns with default values to tables in Oracle (including new 12C features)

Adding columns with default values to tables in Oracle (including new 12C features)

This is a problem that I have been paying attention.

11G has been improved on this issue, and 12C has some new features.

12C adds the new 11g feature in this regard.

Add a column in 11G and set the default value. Instead of changing the actual data in the table, mark the data dictionary.

During the query, an operation like nvl () is performed on the new column. If it is null, the default value is returned.

However, read the following statement:

 

SQL> alter table t add MrDai number default 10000 not null; Table altered. Elapsed: 00:00:00. 34

In 11G, the newly added column must be not null. Otherwise, this feature cannot be implemented. It will still cause a large DDL transaction, and hang will execute all operations on the table (including select ).

 

If you do not understand this feature, it is easy to think that it has a feature, and rashly add it, resulting in a very serious accident.

In 12C, this feature is enhanced again. We do not need to set not null here, which greatly reduces the possibility of misoperations.

See the following experiment process.

Create Table t, with more than 1 million rows of data.

 

SQL> select count (*) from t; COUNT (*) ---------- 1454256

Add a column directly to the table (not null is not set)

 

 

SQL> alter table t add MrDai1 number default 10000; Table altered. Elapsed: 00:00:00. 03

Fast. Of course, it's the same as in 11G.

 

After the setting is complete, if the row is newly inserted, the data will be truly inserted into the row in the default column.