Category Archives: Constraints

Excessive Locking when Dropping a Table

Oren Nakdimon Leave a Reply

I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”. That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys. A quick investigation revealed the cause – the DROP […]

Continue reading

Constraint Optimization Summary

Oren Nakdimon Leave a Reply

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 […]

Continue reading

Fast but Offline, or Online but Slow?

Oren Nakdimon Leave a Reply

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 […]

Continue reading

Adding a Column with a Default Value and a Constraint

Oren Nakdimon Leave a Reply

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 […]

Continue reading

(Lack of) Optimization of Unique Constraint Creation

Oren Nakdimon 1 Reply

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 […]

Continue reading

Optimization of Foreign Key Constraint Creation

Oren Nakdimon Leave a Reply

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 […]

Continue reading

Optimization that Violates Data Integrity

Oren Nakdimon 1 Reply

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 […]

Continue reading

Optimization of Check Constraint Creation

Oren Nakdimon 3 Replies

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 […]

Continue reading