Query transformations in Oracle: or-expansion

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.
Continue reading