Cost-based optimization in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the main goal of cost-based optimization

What is the primary objective of cost-based optimization in a database management system?

A To store query results permanently for faster future access

B To execute queries as quickly as possible without considering resource usage

C To randomly select a query plan to balance load across servers

D To find the query execution plan with the lowest estimated resource usage

💡 Hint

Think about what 'cost' refers to in query execution.

Cost-based optimization aims to choose the query plan that uses the least resources like CPU, memory, and disk I/O, leading to efficient query execution.

📋 Factual

intermediate

Components considered in cost estimation

Which of the following components is NOT typically considered by a cost-based optimizer when estimating the cost of a query plan?

A CPU usage required to process the query

B Disk I/O operations needed to read data

C Network latency between client and server

D Memory usage during query execution

💡 Hint

Consider what the optimizer controls directly during query execution.

Cost-based optimizers focus on resources used by the database engine itself, such as CPU, disk I/O, and memory. Network latency is external and usually not part of cost estimation.

🔍 Analysis

advanced

Analyzing the effect of statistics on cost-based optimization

How do outdated or inaccurate statistics affect the decisions made by a cost-based optimizer?

A They can cause the optimizer to choose inefficient query plans leading to slower execution

B They have no effect since the optimizer uses fixed rules regardless of statistics

C They cause the optimizer to skip cost estimation and use default plans

D They improve optimization by forcing the optimizer to try multiple plans

💡 Hint

Think about how the optimizer estimates costs based on data distribution.

The optimizer relies on statistics about data distribution to estimate costs accurately. If these statistics are outdated, it may misestimate costs and pick suboptimal plans.

❓ Comparison

advanced

Comparing cost-based and rule-based optimization

Which statement best describes a key difference between cost-based and rule-based query optimization?

A Cost-based optimization uses estimated resource costs to choose plans; rule-based uses fixed priority rules without cost estimation

B Rule-based optimization always produces faster query plans than cost-based optimization

C Cost-based optimization ignores data statistics while rule-based relies on them heavily

D Rule-based optimization is more flexible and adapts to changing data distributions automatically

💡 Hint

Consider how each optimizer decides which plan to use.

Cost-based optimizers estimate resource usage costs to pick the best plan, while rule-based optimizers follow fixed rules without estimating costs.

❓ Reasoning

expert

Identifying the impact of join order on query cost

Given a query joining three tables A, B, and C, why does the order in which tables are joined affect the cost estimated by a cost-based optimizer?

A Because the optimizer only supports joining tables in alphabetical order

B Because different join orders can change the size of intermediate results, affecting CPU and I/O costs

C Because join order does not affect cost; all orders produce the same result and cost

D Because the database engine executes joins in parallel regardless of order

💡 Hint

Think about how intermediate data size influences resource use.

Joining tables in different orders can produce larger or smaller intermediate results, which changes the amount of CPU and disk I/O needed, thus affecting the total cost.

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