Category Archives: Data Model Design

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

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 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

A Recipe for Summoning the RBO Monster (even in Oracle 12c): On Delete Cascade, Function-Based Index and Missing Table Statistics

Oren Nakdimon Leave a Reply

The last version of Oracle in which CHOOSE was officially supported as an OPTIMIZER_MODE parameter value was 9.2. This is what the documentation of Oracle 9.2 says about it: choose The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available. If the data dictionary contains statistics for at […]

Continue reading

3 Points about Indexes and Order

Oren Nakdimon 2 Replies

When dealing with indexes, order may be very significant, in several aspects. Perhaps it’s not surprising after all when talking about a data structure that its purpose is to keep data in order… We’ll refer to three different points: 1. Is the order of columns in a composite index important? 2. Which order is better: […]

Continue reading