SQLMDX

Eventually it's all about data

Archive for December, 2013

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 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.
Read the rest of this entry »

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