Category Archives: Optimizer

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

Who Rewrote My SQL?

Oren Nakdimon Leave a Reply

There are several new features in Oracle 12c that are implemented under the hood by changing the SQL statement that we write to a different statement (e.g., by adding some hidden predicates). In OUG Ireland 2016 I talked about two such features – In Database Archiving and Temporal Validity – as part of my “Write […]

Continue reading

When X+0 and X-0 are not the same

Oren Nakdimon Leave a Reply

In the old days, when the Rule Based Optimizer (RBO) ruled, there was a very common technique to help the optimizer choose one plan over the other (if they had the same rank) by preventing the use of an index. Look at the following query, where there are unique indexes on T1.ID and T2.ID and […]

Continue reading

Optimizer bug leads to wrong results

Oren Nakdimon 1 Reply

A few days ago a query that I wrote did not return the expected results. After some investigation I came to conclusion that the reason is a bug of the optimizer. Here is a very simplified example (and quite silly functionality-wise, but I guarantee you that the original, more complex, query does make sense). I […]

Continue reading