Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

November 16, 2021

14:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.


Why are tables missing from the plan? An introduction to optimizer transformations
When you submit a SQL query to the database, it's up to the optimizer to figure out how to run it.

In this process the optimizer will often change the query to help it run faster. This can result in surprising execution plans.

Watch this session to understand why these transformations happen, get an overview of what they are, and steps you can take to ensure optimal SQL.

Highlights include:

03:00 - Why do I need to care about transformations?
04:00 - SQL is NOT a procedural language
06:40 - Transformations are a sign of a good data model
12:00 - Logical vs cost-based transformations
15:00 - How to view the final query after transformations
16:30 - Which constraints aid transformations?
20:40 - Running a 10053 trace to view the final query
23:20 - Example query with no transformations
24:15 - How removing select * changes the plan
24:50 - How to pin execution/explain plans in SQL Developer
25:45 - How enabling foreign keys can change the plan
27:20 - How enabling primary keys can change the plan
31:00 - How enabling not null constraints can change the plan
33:10 - What tools can suggest constraints?
37:45 - Join syntax for queries
37:40 - Select distinct vs rownum < 2
47:10 - Why not write the transformed query at the start?
41:30 - Why might it take several minutes to generate a plan?
51:30 - How always false conditions can change the plan
53:30 - How OR conditions can change the plan
57:00 - Summary

Your Experts

    Chris Saxon

    Chris Saxon   

    Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
    Nigel Bayliss

    Nigel Bayliss   

    I'm the product manager for the Oracle Optimizer. I started out as an Oracle DBA and application developer in 1988, gradually migrating to the field of consulting, specializing in database design and performance management. I joined Oracle Consulting in 1996 where I concentrated on delivering performance-critical, very large database applications. Since then, I've been involved in a wide variety of different roles including development, benchmarking and pre-sales (with a particular emphasis on Oracle Exadata). I am part of the Oracle Data Warehouse Product Management team.