Category Archives: Indexing

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

(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

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

Indexes and Indexing in Oracle 12c

One of my presentations in ilOUG Tech Days 2015 was “Indexes and Indexing in Oracle 12c”. I talked about new features added in release 12c that are related to indexing – not necessarily new keywords in the CREATE INDEX statement, but also changes that may affect the way we index (or not). Here is a […]

Continue reading