Add columns with default values to tables in Oracle (including new features of 11 GB)

Why should we discuss this issue? It is rooted in history. Let's start with Oracle 10 Gb. Before 11G, if you want to add a new column to a large table and the new column has a default value, if you want to use alter table xx add xx_1 number default 1000; this type of statement settings will be very handy!

Its action is to add a column and update the column. If the table is very large, a very large transaction will be generated.

Because it is a ddl operation, it may even cause the select statements about the table to be blocked! The consequences are terrible.

What should we do if we encounter this requirement in 10 Gb?

Simply put, add a column first (without setting the default value)

Then, you can use PL/SQL to submit and update this column in batches (for example, commit in line 1 of Row 3)

The advantage is that it won't lock too many rows, and every transaction is not big. If something goes wrong, it won't produce dead transactions.

Alternatively, you can use the online redefinition method. , Refer to my blog

In 11G, we add a column to the table with a default value, with new features

Let's test and create a table t with more than 1 million data.

SQL> select count (*) from t;

COUNT (*)
----------
1207317

Add a column to the table and set not null to add the default value.

SQL> alter table t add MrDai number default 10000 not null;

Table altered.

Elapsed: 00:00:00. 34

Fast

In fact, only a tag is made in the data dictionary, and the actual data of the row in the block is not modified, so the speed is fast.

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

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

To sum up, this feature is similar to setting a breakpoint for the table. If the column is null before the breakpoint, it is converted to the default value, this column inserts a true value.

This article describes the dump process for this feature. For more information, see.

If you want to make the new columns all belong to null, and the subsequent data has a default value, you can also, in two steps

Alter table t add MrDai number;

Alter table t modify MrDai number default 10000;