Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding column with default value

I have a table A (3 columns) in production which is around 10 million records. I wanted to add one more column to that table and also I want to make default value to 1. Is it going to impact production DB performance If add a column with default value 1 or something else. What would be best approach to this to avoid any kind of performance impact on DB? your thoughts are much appreciated!!

like image 244
user2824874 Avatar asked Oct 07 '13 21:10

user2824874


1 Answers

In Oracle 11g the process of adding a new column with a default value has been considerably optimized. If a newly added column is specified as NOT NULL, default value for that column is maintained in the data dictionary and it's no longer required for a default value of a column to be stored for all records in a table, so it's no longer required to update each record with a default value. Such an optimization considerably reduces amount of time the table is exclusively locked during the operation.

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

Moreover, column with a default value added that way will not consume space, until you deliberately start to update that column or insert a record with a non default value for that column. So the operation of adding a new column with a default value and not null constraint specified completes pretty quick.

like image 184
Nick Krasnov Avatar answered Sep 23 '22 01:09

Nick Krasnov