Skip to Main Content

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 Learn SQL performance tuning https://devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html

Resources

Workshop Info

Session Has Completed - 16 November 2021
1 Hour
Write Great SQL Office Hours
English
SQL

Other Upcoming Sessions