Oracle if updating column


Tom, I have a doubt about adding columns with default values to a table.

oracle if updating column-81

I do see, however, that a new sentece has been added to the 11g documentation regarding this and this is it "For some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML." but again, it doesn't mention what KIND of optimization Oracle does.

I have done very light test adding the column with the default value and not null constraint straight, as opposing of doing it step by step (add the column, update to the default value, then add the not null constraint) and whereas redo generation (according to v$mystat) is reduced, there is no significant reduction in undo generation (this is on 10g R2).

If you want to do this online (80gb is small to medium sized, not very large) you could use dbms_redefinition in 10g.

Be careful when adding not-null columns with default values in 11g if you use triggers.

This is especially problematic if you use Designer-generated table APIs.