When we add in a single ALTER TABLE statement a new nullable with no default value column and a corresponding:
- inline check constraint
or
- foreign key constraint (either inline or out-of-line)
then Oracle does a nice and useful optimization. In these cases Oracle enables the constraint (for future DML statements) and marks the constraint as VALIDATED (stating that existing records respect the constraint) without actually performing any check – counting on the fact that all the existing records have NULL in the new column. If the table already contains many records, then skipping the (unnecessary) validation phase can save lots of time (during which the table is locked in a highly restrictive mode).
But when adding a new column with a (constant) default value, no optimization is done.
Since all the existing records will have the same value in the new column (the default value) – then the validation phase can be very simple and short. Oracle should only check that this single value respects the constraint; there is no reason to visit each and every record in the table for repeating the same validation over and over again, as it is currently done.
For more details please see Adding a Column with a Default Value and a Constraint - @DBoriented