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