Query transformations in Oracle: view merging

Query transformations in Oracle is a part of query optimization.
In short, query optimization can be divided into three parts
1. Logical optimization: query transformation
2. Physical optimization: searching for optimal access paths, join methods, join orders.
3. Cost Estimator: choosing the optimal plan.

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 physical optimization is done by Plan Generator.
Plan costing, comparison and choosing the one with the lowest cost is done by the Cost Estimator.
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 were applied without taking cost into account and with an assumption that they produce faster plan most of time. Since version 10gR1 Oracle introduced cost-based transformations which are applied only in case when transformed query has the lower cost than non-transformed. So in this case Plan Generator loops back to Query Transformer which leads to many more strategies evaluated to find the most efficient plan.
Continue reading