Start after greeting:

“Good Afternoon everyone, and thank you for joining.

Before we dive into SQL query optimization, I want to start with a simple situation that most
of us as developers have experienced.”

(Pause 2 seconds)

“Imagine a production application where a user clicks a button to load some data.
The expectation is the result should come in less than 1 second.”

“But instead, the page keeps loading…

3 seconds…
5 seconds…
sometimes even 15 seconds.”

“At that moment, the user does not care whether the problem is in Java code, API layer, or
database.
For them, the application is simply slow.”

(Pause)

“And in most enterprise systems, the surprising reality is that 70–80% of performance
problems actually originate from inefficient database queries.”

Now Transition to the Slide

“That is exactly why query performance matters.”

Then point to each bullet:

1️⃣ Improve response time

“The first and most visible impact is response time.

A well-optimized query can return results in milliseconds instead of seconds.”

2️⃣ Reduces CPU, Memory & I/O Usage

“When queries are inefficient, the database has to scan more rows, read more disk blocks, and
consume more CPU and memory.
Optimized queries reduce unnecessary resource consumption.”
3️⃣ Lower infrastructure cost

“If the database is constantly overloaded because of poor queries, the usual reaction is to
scale the hardware — more CPUs, more RAM, bigger servers.
But many times the real solution is simply writing better queries.”

4️⃣ Enhances user experience

“And finally, faster queries mean faster applications, which directly improves user
experience and business productivity.”

Transition to Next Slide

“So in this session today, we will see how SQL queries actually get executed internally,
why some queries become slow, and what practical techniques we can use to optimize
them.”

“Let’s start by understanding how the database actually processes a SQL query
internally.”

Opening Line

“Before we learn how to optimize SQL queries, we first need to understand what actually
happens inside the database when we execute a query.”

“Most developers think when we run a query, the database immediately fetches data.
But internally, the database goes through multiple processing stages before returning the
result.”

“Let’s walk through this journey step by step.”

Step 1 — Query Submission

Point to Query

“When a developer writes a SQL statement like”

SELECT * FROM employees WHERE department_id = 10;

“This SQL query is first sent to the database engine.”

“But the database cannot directly understand SQL text.

It first needs to analyze and translate it into an internal representation.”
Step 2 — Parser & Translator
Point to Parser and Translator

“The first component that processes the query is the Parser.”

“The parser performs several checks.”

1️⃣ Syntax check

“Is the SQL statement written correctly?”

Example:

SELECT FROM employees

“This would fail immediately.”

2️⃣ Semantic check

“The database verifies whether the tables and columns actually exist.”

Example:

SELECT salary FROM employees

“If the column or table does not exist, the query fails here.”

3️⃣ Translation

“Once validation is complete, the SQL query is converted into a Relational Algebra
expression, which is the internal language used by the database.”

Step 3 — Relational Algebra Expression

Point to Relational Algebra Expression

“This is an internal representation of the query logic.”

“It describes operations like”

  Selection
 Projection
 Join
 Aggregation

“But this representation does not yet decide the best way to execute the query.”

“That responsibility belongs to the optimizer.”

Step 4 — Optimizer (Most Important)

Point to Optimizer

“This is the brain of the database engine.”

“The optimizer evaluates multiple possible ways to execute the query and selects the most
efficient execution plan.”

Example decisions:

 Should we use index scan or full table scan?

 Which join method to use?
o Nested Loop
o Hash Join
o Merge Join
 Which table should be joined first?

“To make these decisions, the optimizer relies heavily on statistics about the data.”

Point to statistics

“These statistics include”

 Number of rows
 Data distribution
 Index selectivity
 Cardinality

Step 5 — Execution Plan

Point to Execution Plan

“Once the optimizer selects the best strategy, it generates an execution plan.”

“This plan defines the exact sequence of operations the database will perform.”
Examples:

 Table access
 Index scan
 Join operations
 Filtering
 Sorting

“This is the same plan we analyze using tools like”

EXPLAIN PLAN
DBMS_XPLAN.DISPLAY

Step 6 — Evaluation Engine

Point to Evaluation Engine

“The execution engine now runs the execution plan step by step.”

“It retrieves data from the storage layer, performs joins, applies filters, and processes the
results.”

Step 7 — Query Output

Point to Query Output

“Finally, after all operations are completed, the database returns the result set to the user or
application.”

Key Message (Important Closing Line)

“So when we talk about query optimization, we are essentially trying to influence the
optimizer to generate a better execution plan.”

“Because even if two queries return the same result, their execution plans can be completely
different in performance.”

Opening Line

“Now that we understand how a query travels through the database engine, let's look at
something that often confuses developers.”

“When we write a SQL query, we write it in one order… but the database logically processes
it in a completely different order.”
Show a Simple Query Example

You can say:

“For example, when we write a query like this:”

SELECT name, salary

FROM employees
WHERE department_id = 10
ORDER BY salary;

“Most developers assume SQL executes from top to bottom.”

“But internally, SQL does not execute in this order.”

Logical Query Processing Order

Point to the list.

“SQL follows something called Logical Query Processing Order, which defines how the
query is logically evaluated.”

Explain briefly:

1️⃣ FROM

“First the database identifies the source tables.”

2️⃣ JOIN

“If multiple tables are involved, it performs joins.”

3️⃣ ON

“The join condition is applied.”

4️⃣ WHERE

“After rows are combined, filtering happens using the WHERE clause.”
5️⃣ GROUP BY

“If aggregation is used, rows are grouped.”

6️⃣ HAVING

“Filters aggregated results.”

7️⃣ SELECT

“Only now the database selects the columns requested.”

(This surprises many people)

8️⃣ DISTINCT

“Duplicate rows are removed if DISTINCT is used.”

9️⃣ ORDER BY

“Then results are sorted.”

🔟 LIMIT / FETCH

“Finally the database restricts the number of rows returned.”

Important Insight (Very Important Line)

Now point to the last line.

“However, this is only the logical order.”

“The actual execution order is determined by the optimizer and execution plan.”
“That means the database may choose a completely different physical strategy to retrieve
the data efficiently.”

Closing Line
“And this is exactly why execution plans become extremely important when optimizing
queries.”

“In the next section, we’ll see how the optimizer decides the best execution strategy.”

✅ Presentation Tip

When you say SELECT comes after WHERE, pause for a second.
Many developers realize something new there.

You can say:

“Interestingly, SELECT actually happens after WHERE logically, even though we write it
first.”

Opening Line

“Now that we understand the logical order of SQL execution, the next important concept is
how the database actually optimizes queries internally.”

“In modern databases like Oracle, this is handled by something called the Cost Based
Optimizer, or CBO.”

What is Cost Based Optimizer

Point to CBO.

“The Cost Based Optimizer is essentially the decision-making engine of the database.”

“When we submit a SQL query, the optimizer does not directly execute it.
Instead, it first analyzes multiple possible execution strategies.”

“It then calculates the estimated cost of each strategy based on statistics such as”

 Number of rows
 Index availability
 Data distribution
 Cardinality
“And finally, it chooses the execution plan with the lowest cost.”

Query Rewrite / Transformation

Point to Rewrite and transforms the query internally

“One important thing to understand is that the optimizer can rewrite the query internally.”

“So the SQL we write is not always the exact SQL that gets executed internally.”

“The optimizer may transform the query to make it more efficient.”

What the Optimizer Can Change

Point to the list.

Join Order

“If a query involves multiple tables, the optimizer can change the order in which tables are
joined to reduce the number of rows processed.”

Filter Placement

“The optimizer can also move filters earlier in the execution plan to reduce unnecessary
data scanning.”

This is often called predicate pushdown.

Subqueries

“In some cases, the optimizer may transform subqueries into joins or other efficient
operations.”

Aggregation Order

“It can also change how aggregations are processed to minimize intermediate data.”

Key Takeaway (Very Important Line)

“So the important takeaway here is that SQL optimization is not just about writing correct
queries, but about helping the optimizer generate the best execution plan.”

Transition to Next Topic

“In the next section, we’ll look at how to analyze execution plans and identify
performance problems in SQL queries.”

Opening Line

“Now that we understand the logical query processing order, the next question is — why
does this order matter when we are tuning SQL queries?”

“The main reason is that every step in the query pipeline processes data from the
previous step.”

“So if we can reduce the amount of data early, the database has less work to do later.”

Key Concept – Filters Reduce Data Early

Point to the first bullet.

“One of the most important optimization principles is filtering data as early as possible.”

“When the WHERE clause is applied early, it removes unnecessary rows before they go
through joins, grouping, or sorting.”

“This significantly reduces”

 CPU usage
 Memory usage
 Disk I/O

“And overall improves query performance.”

Why This Helps Query Optimization

Point to the second bullet.

“So understanding the logical execution order helps developers visualize when filtering,
grouping, and selection actually happen.”

“This helps us write queries that reduce data earlier in the execution pipeline.”
Walk Through the Example
Point to the example query steps.

“Let’s quickly look at this example.”

Step 1 – FROM

“The database first reads data from the employees table.”

Step 2 – JOIN

“It then joins it with another table, in this case orders, using the join condition.”

Step 3 – WHERE

“After joining, the WHERE condition filters rows, for example salary greater than 3000.”

“This step is critical because it reduces the dataset before aggregation happens.”

Step 4 – GROUP BY

“Next the database groups the remaining rows by department.”

Step 5 – HAVING

“Then the HAVING clause filters aggregated results, such as departments where average
salary is greater than 5000.”

Step 6 – SELECT / DISTINCT

“After filtering and grouping, the database selects the required columns and removes
duplicates if DISTINCT is used.”

Step 7 – ORDER BY
“Finally the result set is sorted.”

Step 8 – LIMIT / FETCH

“And at the end, the database returns only the required number of rows.”

Key Takeaway (Important Final Line)

“So the key takeaway is that the earlier we reduce data in the execution pipeline, the
faster the query becomes.”

“This is why understanding the logical execution order is essential for writing optimized
SQL queries.”

Opening Line

“So far we have discussed how queries are processed internally and how the optimizer
decides the best execution strategy.”

“But the important question for developers is — how do we actually see what the database
is doing internally?”

“That is where Explain Plans become extremely useful.”

What Explain Plan Does

Point to the main line.

“An Explain Plan provides insight into how the database plans to execute a SQL query.”

“It shows the execution plan generated by the optimizer, which includes the sequence of
operations the database will perform to retrieve the data.”

“In simple terms, it helps us understand what the database is actually doing behind the
scenes.”

Key Information Explain Plan Provides

Point to each bullet.
Performance Insight

“First, it gives performance insights, helping us identify slow operations such as full table
scans or expensive joins.”

Cost Analysis

“It also provides cost estimates, which represent the optimizer’s estimate of how expensive
each operation will be.”

“The optimizer uses this cost to decide which execution plan is the most efficient.”

Index Utilization

“Explain Plan also tells us whether the database is using indexes or performing full table
scans.”

“This is critical when tuning queries.”

Join Strategies

“It also shows the join methods used by the optimizer.”

For example:

 Nested Loop Join

 Hash Join
 Merge Join

“Each join strategy has different performance characteristics.”

Troubleshooting

“Finally, Explain Plans help us troubleshoot slow queries by identifying which step in the
execution plan is consuming the most resources.”
Key Takeaway
“So whenever we need to optimize a query, the first step is usually to analyze its execution
plan using Explain Plan.”

“This helps us understand how the optimizer is executing the query and where
improvements can be made.”

Opening Line

“Now let's look at a practical example of an Explain Plan and understand how to interpret
it.”

“At the top, we have the SQL query that joins the employee and department tables and
filters employees with salary greater than 80,000 in department 3.”

Execution Plan Table

Point to the execution plan table.

“This table shows the sequence of operations the database will perform to execute this
query.”

Each row represents one step in the execution process.

Operation Column
Point to Operation.

“The Operation column tells us what action the database is performing.”

For example here we see:

 SELECT STATEMENT – the overall query

 NESTED LOOPS – the join method used
 TABLE ACCESS FULL – full table scan on the employee table
 INDEX UNIQUE SCAN – using an index on the department table

“These operations help us understand how the database retrieves the data.”
Join Type
Point to Nested Loops

“In this case, the optimizer has chosen a Nested Loop Join.”

“This means the database will take rows from one table and for each row search the matching
rows in the other table.”

“This strategy works well when one table returns a small number of rows.”

Index Usage
Point to Index Unique Scan

“Here we can see an Index Unique Scan on the department table, which means the
database is using an index to quickly locate the required department.”

“This is much faster than scanning the entire table.”

Full Table Scan

Point to Table Access Full Employee

“However, we also see a Full Table Scan on the employee table, meaning the database
reads the entire employee table.”

“If the table is large, this could become a performance bottleneck.”

Cost Column
Point to Cost

“The Cost column represents the optimizer’s estimate of the resources required for each
operation.”

“The optimizer compares different plans and selects the one with the lowest estimated cost.”
Predicate Information
Point to the Predicate section

“This section shows the actual filter conditions applied during execution, such as
department id equals 3 and salary greater than 80,000.”

Key Takeaway
“So when tuning queries, we mainly look for things like”

 Full table scans

 Inefficient join methods
 Missing indexes
 High-cost operations

“These indicators help us identify where the query can be optimized.”