Skip navigation

Optimization of adding a column and a unique constraint in a single statement

score 150
You have not voted. Active

When adding in a single ALTER TABLE statement a new column with no default value and a unique constraint on that column, then the implicit unique index is created, by definition, with no entries (as B*Tree indexes do not contain entirely NULL keys).

In this case I suggest that Oracle will create an empty index, without scanning all the table rows.

When the column and constraint are added to a table that already contains many records, this can save a significant amount of time and reduce locking time.

For more details please see (Lack of) Optimization of Unique Constraint Creation - DB Oriented


Vote history