This is the last part of a series about Constraint Optimization.
In this post I’ll summarize the conclusions from the previous parts.
When we add a constraint to an existing table, there are two aspects that are worth taking into consideration: duration and availability.
Duration
When the table contains a significant number of rows, adding a constraint may take a lot of time. In some cases Oracle applies a very nice optimization that can reduce this time to almost zero.
In part 1 we saw that such optimization happens when adding a column and an inline check constraint on that column in a single statement; and that this optimization does not happen for out-of-line check constraint.
In part 2 we saw that this optimization may lead to data integrity violation in some extreme cases.
In part 3 we saw that such optimization happens when adding a column and a foreign key constraint on that column in a single statement (for both inline and out-of-line constraints).
In part 4 we saw that unfortunately this optimization never happens for unique constraints.
In part 5 we saw that this optimization doesn’t happen if the added column is defined with a default value.
Availability
By default, adding a constraint is an offline operation. It means that it cannot start as long as the table is locked by active transactions, and that it blocks DML statements on the table for the duration of the operation. Obviously, the longer the constraint addition takes, the higher the significance of the availability issue.
In part 6 we saw how to add check constraints and foreign key constraints in an online way, by splitting the single operation into several ones.
In part 7 we saw how to add unique constraints in an online way (assuming Enterprise Edition).
Summary
The following table summarizes all of the above:
| Adding a column and an inline constraint in a single statement | Adding a column and an out-of-line constraint in a single statement | Adding a column and a constraint in separate statements | |
|---|---|---|---|
| Check Constraint | Fast | Duration depends on table size | Duration depends on table size |
| Offline | Offline | Online can be achieved | |
| Foreign Key Constraint | Fast | Fast | Duration depends on table size |
| Offline | Offline | Online can be achieved | |
| Unique Constraint | Duration depends on table size | Duration depends on table size | Duration depends on table size |
| Offline | Offline | Online can be achieved in Enterprise Edition |