oracle apex sql query transformation rule

The following statements are valid for Oracle RDBMS versions Oracle 10g or higher as the “Cost based query transformation” (CBQT) feature was introduced with Oracle 10g R1. Query transformation – What is it and why do we need it? If you are interested into this topic you will see that it is really valuable source of information.

So the linked references and white papers are mostly written by “third party” researchers or Oracle employees, but all of these publishers are a very trustable source of information. Let’s check the official documentation and white papers first to get an idea of query transformations.įootnote: Unfortunately the (cost based) query transformation behavior is not officially documented by Oracle in detail. Basically said, the client noticed different costs in an execution plan for the same SQL statement on two different database (exact the same version of course), even if all the object / system statistics (which were exported and imported) and database parameter settings were the same. Initially i did not plan to write a blog post about (cost based) query transformations in general as this is a very complex, wide and continuously changing / improving topic, but then i received an e-mail from a client a few weeks ago with a very simple example of it (the later explained example occurred in a SAP ECC environment). However in most cases there is much more happening under the hood, which leads to some issues from time to time as discussed here on SCN for example. We investigated and talked about some basic cardinality calculations of the Oracle cost based optimizer ( CBO) in some of my previous blog posts.

Oracle apex sql query transformation rule