Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Oren NakdimonApr 18 2017

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

Comments
Post Details
Added on Apr 18 2017
0 comments
1,004 views