Cost-based optimization in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to identify the main goal of cost-based optimization.

DBMS Theory

The primary goal of cost-based optimization is to minimize the [1] of executing a query.

Drag options to blanks, or click blank then click option'

Atime

Bcost

Ccomplexity

Dsyntax

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing cost with time or complexity.

Thinking syntax errors are related to cost-based optimization.

Cost-based optimization aims to reduce the cost (resources used) to execute a query efficiently.

2fill in blank

medium

Complete the code to show what cost-based optimizers use to choose query plans.

DBMS Theory

Cost-based optimizers use [1] to estimate the expense of different query execution plans.

Drag options to blanks, or click blank then click option'

Astatistics

Bheuristics

Csyntax rules

Dindexes

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing heuristics with statistics.

Thinking indexes are directly used for cost estimation.

Cost-based optimizers rely on statistics about data to estimate costs and choose the best plan.

3fill in blank

hard

Fix the error in the statement about cost-based optimization.

DBMS Theory

Cost-based optimization always chooses the query plan with the lowest [1] time.

Drag options to blanks, or click blank then click option'

Acompile

Bnetwork

Cparsing

Dexecution

Attempts:

3 left

💡 Hint

Common Mistakes

Thinking compile time is the main factor.

Confusing network or parsing time with execution time.

The optimizer aims to minimize execution time, not compile, network, or parsing time.

4fill in blank

hard

Fill both blanks to complete the cost estimation formula.

DBMS Theory

Total cost = [1] cost + [2] cost

Drag options to blanks, or click blank then click option'

ACPU

BMemory

CI/O

DNetwork

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing memory or network cost as primary components.

Ignoring I/O cost which is often significant.

The total cost usually includes CPU cost and I/O cost, which are the main resource consumers.

5fill in blank

hard

Fill all three blanks to complete the description of cost-based optimization steps.

DBMS Theory

First, the optimizer collects [1]. Then it generates [2] plans. Finally, it selects the plan with the lowest [3].

Drag options to blanks, or click blank then click option'

Astatistics

Bquery

Ccost

Dexecution

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing execution with cost in the final step.

Thinking the optimizer generates statistics as plans.

The optimizer collects statistics, generates query plans, and selects the plan with the lowest 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