Cost-based optimization in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is cost-based optimization in database management systems?

Cost-based optimization is a method used by database systems to choose the most efficient way to execute a query by estimating the cost of different query plans and selecting the one with the lowest cost.

intermediate

What factors does cost-based optimization consider when estimating the cost of a query plan?

It considers factors like CPU usage, disk I/O, memory usage, and network costs to estimate how expensive a query plan will be to execute.

beginner

Why is cost-based optimization important for database performance?

Because it helps the database system run queries faster and use fewer resources by choosing the best execution plan, improving overall system efficiency.

intermediate

How does cost-based optimization differ from rule-based optimization?

Rule-based optimization uses fixed rules to choose query plans, while cost-based optimization evaluates multiple plans and picks the one with the lowest estimated cost.

intermediate

What role do statistics play in cost-based optimization?

Statistics about data, like table size and index distribution, help the optimizer estimate costs accurately and choose the best query plan.

What does cost-based optimization primarily aim to minimize?

A The number of users accessing the database

B The number of tables in a database

C The size of the database

D The estimated resource cost of executing a query

Cost-based optimization tries to find the query plan with the lowest estimated resource cost.

Which of the following is NOT typically considered in cost estimation?

A User login time

B CPU usage

C Disk I/O

D Memory usage

User login time is unrelated to query execution cost.

What helps the optimizer estimate costs more accurately?

A Number of queries run

B User preferences

C Database statistics

D Network speed only

Database statistics provide data distribution and size information needed for cost estimation.

Which optimization method evaluates multiple query plans before choosing one?

A Cost-based optimization

B Rule-based optimization

C Random optimization

D Manual optimization

Cost-based optimization compares different plans based on estimated costs.

Why might a database use cost-based optimization instead of rule-based?

A Because rule-based is faster always

B To find more efficient query plans by considering actual costs

C To ignore data statistics

D To reduce the number of tables

Cost-based optimization uses cost estimates to find better plans than fixed rules.

Explain what cost-based optimization is and why it is used in databases.

Think about how databases decide the best way to run a query.

Describe the role of statistics in cost-based optimization.

Consider what information the optimizer needs to estimate costs.