Adding Columns with Default Values and Not Null in Oracle 11g
Oracle 11g has a new performance enhancement when adding columns. In the pre-11g releases, adding a new not null column with a default value would have caused a massive update on the entire table, locking it for the operation and generating tons of undo and redo. I’ve seen this happening in production.
Oracle 11g has improved this behaviour by storing the default value in the metadata and making the column addition instantaneous. An example of this feature at work can be seen in 11g Enhancement for ALTER TABLE .. ADD COLUMN Functionality and some bugs regarding sysdate, as pointed out in the comments.
Although this is a welcomed enhancement, there are some unexpected aspects beyond the basic operations.
First, we know default values for new columns are stored in the metadata, but what happens when you change the default?
