Category Archives: SQL

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 In the previous parts of this series I […]

Continue reading

COLLECT DISTINCT in PL/SQL Works in Oracle 12.2

Oren Nakdimon 1 Reply

About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there: Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement. One example is the COLLECT aggregate function with […]

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 In the previous parts of this series I […]

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 In a recent post I showed that Oracle […]

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 In the previous post I showed that Oracle […]

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 I have a table T with many records […]

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

The “Control Freak Trigger” Design Pattern

Oren Nakdimon 1 Reply

Suppose that every time we add records into the T1 table we have to do some additional stuff. One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be […]

Continue reading

INSERT into a View with a GROUP BY Clause

Oren Nakdimon 1 Reply

When I wrote the previous post, about updatable views, I noticed an interesting issue. The documentation says: If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the […]

Continue reading