Query execution plans in DBMS Theory - Step-by-Step Execution

Concept Flow - Query execution plans

Start: Receive SQL Query

↓

Parse Query Syntax

↓

Generate Logical Plan

↓

Optimize Logical Plan

↓

Generate Physical Plan

↓

Execute Plan Steps

↓

Return Query Result

The database receives a SQL query, parses it, creates a logical plan, optimizes it, generates a physical plan, executes it step-by-step, and returns the result.

Execution Sample

DBMS Theory

SELECT name FROM employees WHERE age > 30;

This query selects employee names where their age is greater than 30.

Analysis Table

Step	Action	Details	Result
1	Parse Query	Check syntax of SELECT name FROM employees WHERE age > 30;	Syntax valid
2	Generate Logical Plan	Create plan: Scan employees table, filter age > 30, project name column	Logical plan created
3	Optimize Plan	Choose best method: Use index on age if available	Optimized plan with index scan
4	Generate Physical Plan	Plan steps: Index scan on age, retrieve name	Physical plan ready
5	Execute Plan	Perform index scan, filter rows, collect names	Rows with age > 30 retrieved
6	Return Result	Send list of names to user	Query result returned

💡 Query execution completes after returning the result set.

State Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
Query	SELECT name FROM employees WHERE age > 30;	Logical plan created	Optimized plan with index scan	Physical plan ready	Rows filtered and collected	Result returned
Plan Type	None	Logical plan	Optimized logical plan	Physical plan	Executing plan	Execution complete

Key Insights - 3 Insights

Why does the database create a logical plan before executing the query?

The logical plan breaks down the query into steps without worrying about how to do them efficiently. This is shown in step 2 of the execution_table where the plan is created before optimization.

What is the purpose of the optimization step in query execution?

Optimization chooses the best way to run the query, like using indexes. Step 3 in the execution_table shows the plan being optimized to improve performance.

Why is there a separate physical plan after optimization?

The physical plan details exactly how the database will execute the query on the hardware. Step 4 shows this plan ready for execution, translating the optimized logic into real actions.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 3?

A The logical plan is optimized for better performance

B The physical plan is executed

C The query syntax is checked for errors

D The query result is returned

💡 Hint

Refer to the 'Action' and 'Details' columns in step 3 of the execution_table.

At which step does the database actually retrieve data from the table?

A Step 4

B Step 5

C Step 2

D Step 6

💡 Hint

Check the 'Action' column for when the plan is executed and rows are collected.

If the employees table has no index on age, which step would change the most?

A Step 1: Parsing

B Step 5: Execution

C Step 3: Optimization

D Step 6: Returning result

💡 Hint

Look at step 3 where the optimizer chooses the best method; lack of index affects optimization.

Concept Snapshot

Query execution plans break down SQL queries into steps:
1. Parse query syntax
2. Create logical plan (what to do)
3. Optimize plan (best way to do it)
4. Generate physical plan (how to do it)
5. Execute plan steps
6. Return results
This process helps databases run queries efficiently.

Full Transcript

When a database receives a SQL query, it first checks the syntax to ensure it is correct. Then, it creates a logical plan that outlines what steps are needed to get the data. Next, the database optimizes this plan to find the fastest way to run it, such as using indexes. After optimization, a physical plan is made that details exactly how to perform each step on the hardware. The database then executes these steps, retrieves the data, and finally returns the results to the user. This step-by-step process is called the query execution plan and helps the database run queries efficiently and quickly.

Practice

1. What is the main purpose of a query execution plan in a database?

easy

A. To backup the database

B. To show how the database will execute a query step-by-step

C. To create new tables automatically

D. To store the query results permanently

Solution

1. Step 1: Understand what a query execution plan is

   A query execution plan explains the steps the database takes to run a query.

2. Step 2: Identify the main purpose

   The plan helps users see how the database processes the query to optimize performance.

3. Final Answer:

   To show how the database will execute a query step-by-step -> Option B

4. Quick Check:

   Query execution plan = execution steps [OK]

Hint: Execution plans show query steps clearly [OK]

Common Mistakes:

• Confusing plans with data storage
• Thinking plans create tables
• Assuming plans backup data

2. Which SQL command is commonly used to view the query execution plan before running a query?

easy

A. SHOW PLAN

B. RUN

C. EXPLAIN

D. DESCRIBE

Solution

1. Step 1: Recall the command to view execution plans

   The SQL command EXPLAIN is used to display how a query will be executed.

2. Step 2: Differentiate from other commands

   RUN executes queries, SHOW PLAN is not standard, and DESCRIBE shows table structure.

3. Final Answer:

   EXPLAIN -> Option C

4. Quick Check:

   View plan = EXPLAIN [OK]

Hint: Use EXPLAIN to see query plans [OK]

Common Mistakes:

• Using RUN to view plans
• Confusing DESCRIBE with EXPLAIN
• Assuming SHOW PLAN is standard

3. Given the query SELECT * FROM employees WHERE department_id = 5;, what does the execution plan likely show?

medium

A. A full table scan of employees

B. A join operation with another table

C. A sort operation on employee names

D. An index scan on department_id if indexed

Solution

1. Step 1: Analyze the query condition

   The query filters employees by department_id = 5. If department_id has an index, the database uses it.

2. Step 2: Understand execution plan behavior

   If indexed, the plan shows an index scan to quickly find matching rows instead of scanning the whole table.

3. Final Answer:

   An index scan on department_id if indexed -> Option D

4. Quick Check:

   Indexed filter = index scan [OK]

Hint: Indexed columns use index scan in plans [OK]

Common Mistakes:

• Assuming full table scan always happens
• Expecting join when none exists
• Thinking sorting is automatic

4. A query execution plan shows a full table scan but the query filters on a column that has an index. What is the likely cause?

medium

A. The query uses a function on the indexed column

B. The index is corrupted or unusable

C. The database always prefers full scans

D. The table is empty

Solution

1. Step 1: Understand why indexes may be ignored

   If a query applies a function (like LOWER or CAST) on an indexed column, the index cannot be used directly.

2. Step 2: Rule out other options

   Corrupted indexes are rare and usually cause errors; databases do not always prefer full scans; empty tables do not cause full scans.

3. Final Answer:

   The query uses a function on the indexed column -> Option A

4. Quick Check:

   Functions on indexed columns block index use [OK]

Hint: Functions on indexed columns disable index use [OK]

Common Mistakes:

• Assuming index corruption without error
• Believing full scans are default
• Thinking empty tables cause scans

5. You want to optimize a slow query that joins two large tables. The execution plan shows a nested loop join causing delays. What is a good approach to improve performance?

hard

A. Create indexes on the join columns to enable hash or merge joins

B. Remove all indexes to force full table scans

C. Rewrite the query to use subqueries instead of joins

D. Increase the database cache size without changing the query

Solution

1. Step 1: Identify the cause of slow join

   Nested loop joins are slow on large tables without indexes on join columns.

2. Step 2: Apply indexing to improve join method

   Creating indexes on join columns allows the database to use faster join methods like hash or merge joins.

3. Step 3: Evaluate other options

   Removing indexes slows queries; rewriting to subqueries may not help; increasing cache alone may not fix join inefficiency.

4. Final Answer:

   Create indexes on the join columns to enable hash or merge joins -> Option A

5. Quick Check:

   Indexes on join keys speed joins [OK]

Hint: Index join columns to speed up joins [OK]

Common Mistakes:

• Dropping indexes thinking it helps
• Assuming subqueries are always faster
• Relying only on cache size increase