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

Need a 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

Need a 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

Need a 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

Need a hint?

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