Abstract
We have seen throughout this book that there are many ways to transform one SQL query (or DML subquery) into another without in any way changing the query's meaning. For all but the most trivial of queries the CBO will consider a number of such transformations, sometimes estimating costing, to determine the best SQL statement on which to perform the final state optimization that we just covered in Chapter 12. This chapter is dedicated to the topic of optimizer transformations; you will see that there are a bewildering number of such transformations. Although not all optimizer transformations are included in this chapter, the vast majority that you will encounter in your day-to-day life are, as well as some that you will rarely encounter.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Notes
- 1.
I have reason to believe that there may actually be a bug in 12.1.0.1.
- 2.
Restrictions on complex view merging preclude reversing the transformation in Listing 13-17.
- 3.
Occasionally an INDEX FAST FULL SCAN will not be materialized unless hinted even when referenced multiple times.
- 4.
Actually Jonathan Lewis has shown me a counterexample that uses the SEMIJOIN_DRIVER hint but the CBO will never generate such a plan without such nefarious hinting.
- 5.
The academic paper didn’t use the SH example schema but I have produced an equivalent example.
- 6.
They are equivalent after the correction of what appears to be a typographical error.
Author information
Authors and Affiliations
Rights and permissions
Copyright information
© 2014 Tony Hasler
About this chapter
Cite this chapter
Hasler, T. (2014). Optimizer Transformations. In: Expert Oracle SQL. Apress, Berkeley, CA. https://doi.org/10.1007/978-1-4302-5978-7_13
Download citation
DOI: https://doi.org/10.1007/978-1-4302-5978-7_13
Published:
Publisher Name: Apress, Berkeley, CA
Print ISBN: 978-1-4302-5977-0
Online ISBN: 978-1-4302-5978-7
eBook Packages: Professional and Applied ComputingProfessional and Applied Computing (R0)Apress Access Books