Insights into Statistics, Query Optimization and the Oracle Optimizer
Transformations
Optimizer Transformations: Table Expansion
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...
Tuesday, April 26, 2011 | Transformations | Read More
Optimizer Transformations: OR Expansion
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 further...
Monday, April 11, 2011 | Transformations | Read More
Optimizer Transformations: Join Factorization
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...
Friday, February 25, 2011 | Transformations | Read More
Optimizer Transformation: Join Predicate Pushdown
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...
Monday, January 3, 2011 | Transformations | Read More
Optimizer Transformations: Star Transformation
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...
Thursday, November 4, 2010 | Transformations | Read More
Optimizer Transformations: View Merging part 2
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 orders...