Eventually it's all about data

Recursive calls and statistics

Posted by sqlmdx on May 11, 2014

There are many cases when executing some statement (either SQL or PL/SQL) may invoke execution of another SQL or PL/SQL statement.
However it’s not at all obvious how execution statistics for cursors are aggregated.
Read the rest of this entry »

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

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 »

Query transformations in Oracle: view merging

Posted by sqlmdx on November 14, 2013

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

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

The Power of Oracle SQL

Posted by sqlmdx on March 15, 2011

This page contains all versions of my book The Power of Oracle SQL (rus).

Published title Oracle SQL Revealed (eng).



Some changes in compare to Russian version:

Part I

  • First chapter was thoroughly refactored in order to make it easier to read. It has a huge number of examples and they have been structured a bit better.
  • New chapter “Aggregate Functions” (it also includes information about pivoting/unpivoting).
  • Some details about performance and internal implementation were added regarding pattern matching.
  • Chapter “Logical Execution Order of Query Clauses” was completely reworked. In Russian version it contains a few inaccuracies which may lead to wrong conclusions.

Part II

  • Solutions for a few of quizzes have been improved or changed. In particular, for Ordering Dependencies and Resemblance Group.
  • Some new approaches have been introduced, for instance, for Zeckendorf Representation, etc.


Posted in Oracle | Leave a Comment »