Cost-based optimization in DBMS Theory - Step-by-Step Execution

Concept Flow - Cost-based optimization

Receive SQL Query

↓

Parse Query

↓

Generate Possible Execution Plans

↓

Estimate Cost for Each Plan

↓

Compare Costs

↓

Choose Lowest Cost Plan

↓

Execute Chosen Plan

The database receives a query, creates many ways to run it, estimates the cost for each, and picks the cheapest plan to run.

Execution Sample

DBMS Theory

SELECT * FROM Orders WHERE CustomerID = 5;

The database decides how to get orders for customer 5 with the least cost.

Analysis Table

Step	Action	Details	Cost Estimate	Decision
1	Parse Query	Identify tables and conditions	-	Proceed
2	Generate Plan A	Full table scan of Orders	Cost = 100	Consider
3	Generate Plan B	Use index on CustomerID	Cost = 10	Consider
4	Compare Costs	Plan A vs Plan B	-	Plan B cheaper
5	Choose Plan	Select Plan B	-	Execute Plan B
6	Execute Plan	Retrieve matching rows using index	-	Done

💡 Execution stops after choosing and running the lowest cost plan (Plan B).

State Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Query	SELECT * FROM Orders WHERE CustomerID = 5;	Parsed	Parsed	Parsed	Parsed
Plan A Cost	N/A	100	100	100	100
Plan B Cost	N/A	N/A	10	10	10
Chosen Plan	None	None	None	Plan B	Plan B

Key Insights - 3 Insights

Why does the optimizer consider multiple plans instead of just one?

Because different plans can have very different costs. The optimizer compares them all (see execution_table steps 2 and 3) to pick the cheapest.

What does 'cost' mean in cost-based optimization?

Cost is an estimate of resources like time and CPU needed to run a plan. Lower cost means faster or cheaper execution (see execution_table cost estimates).

Why might a full table scan have a higher cost than using an index?

A full scan reads every row, which is slow for large tables. Using an index targets only needed rows, so it costs less (see Plan A vs Plan B costs).

Visual Quiz - 3 Questions

Look at the execution_table, what is the cost estimate for Plan B?

A 100

B 10

C 50

D 0

💡 Hint

Check the 'Cost Estimate' column at Step 3 in the execution_table.

At which step does the optimizer decide which plan to execute?

A Step 5

B Step 2

C Step 4

D Step 6

💡 Hint

Look for the step labeled 'Choose Plan' in the execution_table.

If the index on CustomerID was missing, which plan would likely be chosen?

A A new plan with cost 5

B Plan B with cost 10

C Plan A with cost 100

D No plan would be chosen

💡 Hint

Refer to the variable_tracker where Plan B cost is only available if index exists.

Concept Snapshot

Cost-based optimization chooses the best way to run a query by:
- Parsing the query
- Generating multiple execution plans
- Estimating the cost of each plan
- Selecting the plan with the lowest cost
- Executing that plan
Cost estimates reflect resource use like time and CPU.

Full Transcript

Cost-based optimization is a process used by databases to run queries efficiently. When a query is received, the database parses it to understand what data is needed. Then, it creates different possible ways to get that data, called execution plans. Each plan is given a cost estimate based on how much time and resources it might take. The optimizer compares these costs and picks the plan with the lowest cost to run. For example, it might choose to use an index instead of scanning the whole table because the index plan costs less. This process helps queries run faster and saves resources.