Query rewrite an one of Query Transformations which makes possible to use materialized view to get results instead of querying underlying tables.
Basic prerequisites for query rewrite described in corresponding chapter of a Database Data Warehousing Guide. Let’s start with a simple example to show how it works.
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 they are applied without cost comparison while cost-based transformations not always lead to better plans so they are applied only if the cost of transformed query block is less than the cost of the 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 others such as “join factorization” can either improve the plan or make it worse thus former is treated as heuristic-based transformation while latter as a cost-based.
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.