The Constraint Optimization series:
- Part 1: Optimization of Check Constraint Creation
- Part 2: Optimization that Violates Data Integrity
- Part 3: Optimization of Foreign Key Constraint Creation
- Part 4: (Lack of) Optimization of Unique Constraint Creation
- Part 5: Adding a Column with a Default Value and a Constraint
- Part 6: Fast but Offline, or Online but Slow?
- Part 7: Adding a Unique Constraint in an Online Way
- Part 8: Constraint Optimization Summary
In the previous parts of this series I showed that Oracle does a nice optimization – that may save plenty of time – when we add in a single ALTER TABLE statement a new nullable with no default value column and a corresponding:
– inline (a.k.a. “column-level”) check constraint
or
– foreign key constraint (either inline or out-of-line)
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. This is a beautiful optimization – if the table already contains many records, then skipping the (unnecessary) validation phase can save lots of time.
So, is it always better to add the new column and the corresponding constraint in a single ALTER TABLE statement? No, it isn’t. Sorry for the cliché, but… it depends.
The advantage is clear: skipping the unnecessary validation phase can save lots of time (the bigger the table, the higher the saving).
But there is also a downside: such a combined operation is an offline one. The table is locked; although for a short time, but in eXclusive mode.
If we cannot afford to do it in one short offline operation, we can do it in three separate online operations, but without the aforementioned optimization:
1. Adding the column (a short operation)
alter table t add (c number);
2. Adding the constraint without validating it (a short operation)
alter table t add (constraint c_chk check (c>0) enable novalidate);
3. Validating the constraint (the bigger the table, the longer this operation takes)
alter table t enable validate constraint c_chk;
By default, when we add a constraint it is both enabled and validated, and this is done as an offline operation. This is why I split it into two steps – both are online: the first step is adding the constraint and enabling it without validation, and the second one is validating the constraint.