A m Z o n e

The truth about DEFAULT values in a table

What is the key difference between the following two set of statements, as far as assigning default value is concerned:

   1.  alter table am01 add col2 varchar2(1) default 'N'; 
   2.  alter table am01 add col2 varchar2(1); 
       alter table am01 modify col2 default 'N'; 

The default value in the first case is applicable for both existing and new records. The default value in the second case is applicable only for new records, existing records are not updated. The First statement requires more resources and is prone to fail for a table with huge number of records, if the rollback segment runs out of space. The second example is applicable in cases where the first is prone to fail. You will have to create a script to manually update the existing records with the default value (committing after every 30,000 or so records). Though time consuming, this may be the only option left.

Press the Back button of you Browser to go to previous page
Home