The table expansion transformation was introduced in 11gR2, to improve performance of a specific category of queries. If you understand how the transformation...
The table expansion transformation was introduced in 11gR2, to improve performance of a specific category of queries. If you understand how the transformation works, you can tailor your indexing scheme to improve plans. We added the transformation based on a few key observations: Index-based plans can improve performance dramatically. If you don't already believe that, check out our post on star transformation for one example. Index maintenance causes overhead to DML, which...
The table expansion transformation was introduced in 11gR2, to improve performance of a specific category of queries. If you understand how the transformation works, you can tailor your indexing...
We continue our series on Optimizer transformations with OR expansion. A Note On Oracle Database 12c Release 2 Note that Oracle Database 12c Release 2 replaces...
We continue our series on Optimizer transformations with OR expansion. A Note On Oracle Database 12c Release 2 Note that Oracle Database 12c Release 2 replaces the OR expansion with the Cost Base OR Expansion Transformation. This will be the subject of a later blog post but, for now, bear in mind that this new transformation has similar benefits to the OR expansion but there some differences: CONCATENATION is replaced with UNION-ALL. Each UNION-ALL branch can be subject to...
We continue our series on Optimizer transformations with OR expansion. A Note On Oracle Database 12c Release 2 Note that Oracle Database 12c Release 2 replaces the OR expansion with the Cost Base OR...
We continue our series on optimizer transformations with a post that describes the Join Factorization transformation. The Join Factorization transformation was...
We continue our series on optimizer transformations with a post that describes the Join Factorization transformation. The Join Factorization transformation was introduced in Oracle 11g Release 2 and applies to UNION ALL queries. Union all queries are commonly used in database applications, especially in data integration applications. In many scenarios the branches in a UNION All query share a common processing, i.e, refer to the same tables. In the current Oracle execution...
We continue our series on optimizer transformations with a post that describes the Join Factorization transformation. The Join Factorization transformation was introduced in Oracle 11g Release 2 and...
Happy New Year to all of our readers! We hope you all had a great holiday season. We start the new year by continuing our series on Optimizer transformations....
Happy New Year to all of our readers! We hope you all had a great holiday season. We start the new year by continuing our series on Optimizer transformations. This time it is the turn of Predicate Pushdown. I would like to thank Rafi Ahmed for the content of this blog.Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with...
Happy New Year to all of our readers! We hope you all had a great holiday season. We start the new year by continuing our series on Optimizer transformations. This time it is the turn of...
Star transformation was introduced in Oracle 8i to process star queries efficiently. These queries are commonly used in data warehouse applications that follow...
Star transformation was introduced in Oracle 8i to process star queries efficiently. These queries are commonly used in data warehouse applications that follow the Star Schema data model. The Star Schema is so called because the data model diagram resembles a star. The center of the star consists of one or more fact tables and the points of the star are the dimension tables. The basic idea of this transformation is to steer clear of using a full table scan access method on...
Star transformation was introduced in Oracle 8i to process star queries efficiently. These queries are commonly used in data warehouse applications that follow the Star Schema data model. The Star...
This is the second of two posts on view merging. See the first post for the basics of view merging, how it works for simple select-project-join views and views...
This is the second of two posts on view merging. See the first post for the basics of view merging, how it works for simple select-project-join views and views appearing in outer joins, and why one of these views might not be merged. In this post we'll cover complex view merging. Complex View Merging We use the term "complex view merging" to describe merging of group by and distinct views. Like simple view merging, this allows the optimizer to consider additional join...
This is the second of two posts on view merging. See the first post for the basics of view merging, how it works for simple select-project-join views and views appearing in outer joins, and why one of...
We continue our series on query transformations with a two-part discussion of view merging. In these posts, we will review the Oracle terminology related to...
We continue our series on query transformations with a two-part discussion of view merging. In these posts, we will review the Oracle terminology related to view merging, explain the different types of view merging, and discuss the reasons that a view might not be merged. The examples in these posts use the Oracle sample schemas. We use the term view to describe a sub-query block appearing in the FROM clause. Oracle can merge several different types of views: Simple view...
We continue our series on query transformations with a two-part discussion of view merging. In these posts, we will review the Oracle terminology related to view merging, explain the different types...
In Part one of our blog series on Optimizer transformations we discussed Subquery unesting. We explained how subqueries are evaluated, and the unnesting of...
In Part one of our blog series on Optimizer transformations we discussed Subquery unesting. We explained how subqueries are evaluated, and the unnesting of EXISTS and ANY subqueries. Here in part two of our series we will discuss the unnesting of NOT EXISTS and single-row subqueries, which appear in the WHERE clause. Once again I need to give credit to Rafi one of the senior Optimizer developers for the content of this blog! Quick recap on what we mean by Subquery Unesting...
In Part one of our blog series on Optimizer transformations we discussed Subquery unesting. We explained how subqueries are evaluated, and the unnesting of EXISTS and ANY subqueries. Here in part two...
We have received a ton of requests for more information on Oracle Optimizer Transformations so we thought we would put together a series of blog posts...
We have received a ton of requests for more information on Oracle Optimizer Transformations so we thought we would put together a series of blog posts describing the most commonly used transformations. You should expect to see two blog posts a month on this over the next quarter. Before we begin we should explain what we mean by transformation? When a SQL statement is parsed, the Optimizer will try to "transform" or rewrite the SQL statement into a semantically equivalent SQL...
We have received a ton of requests for more information on Oracle Optimizer Transformations so we thought we would put together a series of blog posts describing the most commonly...
