askMLabs

Adding a column with default value

Objective :
===========
I want to add a NOT NULL column with some default value to a table which is not empty.

Oracle 11g provides a command something like this ..

SQL> alter table product add Item_code varchar2(20) default 'AAAAA' not null;

Here i am trying to add a column item_code which is not null to a non empty table, and i am specifying a default column value as 'AAAAA'.

It will add a column and for subsequent record inserts it will give the default value to the column item_code if i dont provide a value.

Then what about the column value for already existing records in the table. Will it be NULL ?

Will it try to update the default value for the existing rows. Suppose if there are some millions of records in the table and updating millions of rows will not only take a very long time, it will also fill up the undo segments, generate a large amount of redo, and create massive performance overhead.

Is there any better approach in oracle 11g.

Well we have ....

The above statement will not issue an update to all the existing records of the table. When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary and returns it to the user.

So there is not storage involved , no redo and undo generation and no performance overhead.

Conclusion :
=============
No storage involved. Default value information for existing rows is stored in data dictionary.