Just another WordPress.com site

Query transformations in Oracle. Join elimination.

Posted by sqlmdx on December 14, 2013

Join elimination appeared in Oracle version 10gR2.
Read the rest of this entry »

Posted in Oracle | Tagged: , , | Leave a Comment »

Query transformations in Oracle. Or-expansion.

Posted by sqlmdx on December 8, 2013

As you may know there are two types of query transformations in Oracle: heuristic-based and cost-based.
Heuristic-based transformations are supposed to produce better plan all the time so it’s being applied without cost comparison while cost-based ones not always lead to better plan so they are being applied only if the cost of transformed query block is less then the cost of original one.

I’m not sure whether full list of transformations is publicly available but some brief classification could be find in a white paper Query Optimization in Oracle Database 10g Release 2.

It’s obvious that some transformations such as “outer join to inner join conversion” always lead to better plan while another like “join factorization” can either improve or degrade it thus former is treated as heuristic-based transformation while latter as cost-based.
Read the rest of this entry »

Posted in Oracle | Tagged: , , | Leave a Comment »

Query transformations in Oracle. View merging.

Posted by sqlmdx on November 14, 2013

Transformations in Oracle is a part of query optimization.
In short, query optimization can be divided into two parts
1. Logical optimization: query transformation
2. Physical optimization: searching for optimal access paths, join methods, join orders.
Additional details can be found in a presentation Query Transformations by Joze Senegacnik.

In terms of Oracle SQL Engine, logical optimization is performed by Query Transformer block while Plan Generator is responsible for physical optimization. Third part of a query optimizer is Cost Estimator which makes possible to compare different plans and choose one with the lowest cost.
Additional details regarding Oracle SQL Engine can be found in a VLDB paper Closing the Query Processing Loop in Oracle 11g.

In older Oracle versions there were only so called heuristic-based transformations. They have been applied without taking cost into account and with an assumption that they produce faster plan most of the time. Since version 10gR1 Oracle introduced cost-based transformations which are being applied only in case when transformed query has the lower cost than non-transformed. So in this case Plan Generator makes a loopback to Query Transformer which leads to many more strategies to find the most efficient plan.
Read the rest of this entry »

Posted in Oracle | Tagged: , , | Leave a Comment »


Get every new post delivered to your Inbox.