Table in Oracle adds a discussion with a default value column (including 12C new features)

This is a question that I have always been concerned about.

11G about this problem has improved, to 12C more is a number of new features

For a discussion of the new features of 11G, refer to my blog http://blog.csdn.net/dbcrocodile/article/details/21170407

12C on this aspect is added on the new 11G feature.

Adding a column to the 11G and setting the default value does not make changes to the actual data in the table, but rather by marking the data dictionary

When we query, we do an operation like NVL () for the new column, and if it is NULL, the default value is returned.

However, look at the following statement

Sql> ALTER TABLE t add Mrdai number default 10000 NOT NULL;    Table altered.    elapsed:00:00:00.34  

In 11G, the new plus column must be a NOT NULL property, otherwise the attribute will not be implemented. will still cause large DDL transactions, and hang all operations on the table (including select).

If you do not understand the characteristics, it is easy to think that there are characteristics, and rushed to add, resulting in a very serious accident.

And in 12C, this feature has been strengthened again, we can not set the NOT NULL here, greatly reducing the possible error operation

See the following experimental process

CREATE table T, where more than 1 million rows of data.

Sql> Select COUNT (*) from T;  COUNT (*)----------   1454256

Add a column directly to the table (not set NOT NULL)

Sql> ALTER TABLE t add MRDAI1 number default 10000; Table altered. elapsed:00:00:00.03

The speed is swift. And, of course, as in 11G.

After the setting is complete, if it is a newly inserted row, in the default Value column, the data is actually inserted into the row

Table in Oracle adds a discussion with a default value column (including 12C new features)