Adding a default column

Question

I have a table A(3 columns) in production, which is about 10 million records. I wanted to add another column to this table, and I also want to make the default value equal to 1. Will this affect the performance of the production database If I add a column with the default value of 1 or something else. What would be the best approach to this to avoid any performance impact on the database? your thoughts are greatly appreciated!

Answer 1

In Oracle 11g, the process of adding a new column with a default value has been greatly optimized. If the new column is specified as NOT NULL, the default value for this column is stored in the data dictionary and is no longer required for the default value for the column, which will be stored for all records in the table, so it is no longer required to update each record with the default value. Such optimization significantly reduces the time during which the table is locked exclusively during the operation.

 alter table <tab_name> add(<col_name> <data_type> default <def_val> not null)

, , , , . , NOT NULL .

Answer 2

, :

 create table BackUpTable as SELECT * FROM YourTable;
 alter table BackUpTable add (newColumn number(5,0)default 1);