Cost-based optimization in DBMS Theory - Mini Project: Build & Apply

Understanding Cost-Based Optimization in Databases

📖 Scenario: You are working with a database that stores information about books in a library. You want to understand how the database chooses the best way to run queries efficiently using cost-based optimization.

🎯 Goal: Build a simple example that shows how cost-based optimization can be applied to choose the best query plan based on estimated costs.

📋 What You'll Learn

Create a table called Books with columns BookID, Title, and Author

Insert sample data into the Books table

Create a variable or setting to represent the cost of scanning the entire table

Write a query that uses cost-based optimization principles to choose between a full table scan and an index scan

💡 Why This Matters

🌍 Real World

Cost-based optimization is used by database systems to run queries efficiently, saving time and computing resources.

💼 Career

Understanding cost-based optimization helps database administrators and developers write better queries and tune database performance.

1

Create the Books table with sample data

Write SQL code to create a table called Books with columns BookID (integer), Title (text), and Author (text). Then insert these exact rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'To Kill a Mockingbird', 'Harper Lee').

DBMS Theory

-- Create the Books table and insert sample data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Define a cost variable for full table scan

Add a SQL variable or comment that represents the cost of scanning the entire Books table as full_scan_cost = 100.

DBMS Theory

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  Author TEXT
);

INSERT INTO Books (BookID, Title, Author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Define full_scan_cost variable
-- Your code here

Hint

You can use a comment or a variable declaration depending on your SQL dialect.

3

Write a query to select books by author using cost-based logic

Write a SQL query that selects all columns from Books where Author = 'George Orwell'. Assume the database will choose between a full table scan or an index scan based on cost. Use a comment to indicate that the optimizer compares full_scan_cost with an index_scan_cost of 10.

DBMS Theory

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  Author TEXT
);

INSERT INTO Books (BookID, Title, Author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Cost variable for full table scan
-- full_scan_cost = 100

-- Select books by George Orwell
-- Your code here

Hint

Write a simple SELECT query with a WHERE clause for the author.

4

Add a comment explaining cost-based optimization decision

Add a comment below the query explaining that the database uses cost-based optimization to choose the plan with the lowest cost, comparing full_scan_cost and index_scan_cost.

DBMS Theory

CREATE TABLE Books (
  BookID INTEGER,
  Title TEXT,
  Author TEXT
);

INSERT INTO Books (BookID, Title, Author) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
  (2, '1984', 'George Orwell'),
  (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Cost variable for full table scan
-- full_scan_cost = 100

-- Query to select books by George Orwell
-- The optimizer compares full_scan_cost (100) with index_scan_cost (10) to choose the best plan
SELECT * FROM Books WHERE Author = 'George Orwell';

-- Your code here

Hint

Explain in simple words how the optimizer picks the cheaper plan.

Practice

1. What is the main goal of cost-based optimization in a database system?

easy

A. To find the most efficient way to execute a query

B. To store data in the smallest space possible

C. To encrypt data for security

D. To backup the database automatically

Solution

1. Step 1: Understand the purpose of cost-based optimization

   Cost-based optimization evaluates different ways to run a query and estimates their costs.

2. Step 2: Identify the main goal

   The goal is to pick the plan with the lowest cost, meaning the fastest or least resource-heavy execution.

3. Final Answer:

   To find the most efficient way to execute a query -> Option A

4. Quick Check:

   Cost-based optimization = efficient query execution [OK]

Hint: Focus on efficiency and speed of query execution [OK]

Common Mistakes:

• Confusing optimization with data storage
• Thinking it handles security tasks
• Assuming it manages backups

2. Which of the following is a key input used by cost-based optimizers to estimate query costs?

easy

A. User login credentials

B. Data statistics like table size and index availability

C. Network bandwidth

D. Database backup schedules

Solution

1. Step 1: Identify what cost-based optimizers use

   They rely on data statistics such as table size, number of rows, and indexes to estimate costs.

2. Step 2: Match the correct input

   Data statistics directly affect the cost estimation, unlike user credentials or backup schedules.

3. Final Answer:

   Data statistics like table size and index availability -> Option B

4. Quick Check:

   Cost estimation uses data statistics [OK]

Hint: Remember: statistics guide cost estimates, not user info [OK]

Common Mistakes:

• Confusing user data with statistics
• Thinking network or backups affect cost estimation
• Ignoring the role of indexes

3. Consider a database query optimizer that chooses between two plans: Plan A costs 50 units, Plan B costs 80 units. Which plan will the optimizer select?

medium

A. Neither plan because cost is ignored

B. Plan B because it has a higher cost

C. Both plans equally because cost does not matter

D. Plan A because it has a lower cost

Solution

1. Step 1: Understand cost comparison

   The optimizer picks the plan with the lowest estimated cost to improve performance.

2. Step 2: Compare given costs

   Plan A costs 50 units, which is less than Plan B's 80 units, so Plan A is preferred.

3. Final Answer:

   Plan A because it has a lower cost -> Option D

4. Quick Check:

   Lower cost plan chosen = Plan A [OK]

Hint: Choose the plan with the smallest cost number [OK]

Common Mistakes:

• Picking higher cost plan mistakenly
• Ignoring cost values
• Assuming cost is irrelevant

4. A cost-based optimizer is not choosing the fastest query plan. What could be a likely reason?

medium

A. The data statistics are outdated or inaccurate

B. The database server is turned off

C. The query syntax is incorrect

D. The user has no permissions

Solution

1. Step 1: Identify factors affecting optimizer decisions

   The optimizer depends on accurate data statistics to estimate costs correctly.

2. Step 2: Analyze the problem cause

   If statistics are outdated, the optimizer may pick a suboptimal plan, causing slower queries.

3. Final Answer:

   The data statistics are outdated or inaccurate -> Option A

4. Quick Check:

   Outdated stats cause wrong plan choice [OK]

Hint: Check if statistics are current to fix optimizer issues [OK]

Common Mistakes:

• Blaming server status without checking stats
• Confusing syntax errors with optimization issues
• Assuming permissions affect plan choice

5. A database has two indexes on a table: one on column A and another on column B. A query filters on both columns. How does cost-based optimization decide which index to use?

hard

A. It ignores indexes and does a full table scan

B. It always uses the index on column A by default

C. It estimates the cost of using each index and picks the cheaper one

D. It uses both indexes simultaneously without cost estimation

Solution

1. Step 1: Understand index selection by cost-based optimizer

   The optimizer calculates the cost of using each index based on statistics like selectivity and size.

2. Step 2: Apply cost comparison to index choice

   It chooses the index that results in the lowest estimated cost for the query execution.

3. Final Answer:

   It estimates the cost of using each index and picks the cheaper one -> Option C

4. Quick Check:

   Index choice based on cost estimation [OK]

Hint: Optimizer picks index with lowest estimated cost [OK]

Common Mistakes:

• Assuming fixed index usage without cost check
• Thinking optimizer ignores indexes
• Believing it uses multiple indexes without cost analysis