Query optimization strategies in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the Role of Indexes in Query Optimization

Which of the following best explains how indexes improve query performance in a database?

A Indexes store a sorted copy of data that allows faster searching without scanning the entire table.

B Indexes duplicate all data in the table to speed up data retrieval.

C Indexes compress the data to reduce storage space and speed up queries.

D Indexes automatically update the database schema to optimize queries.

💡 Hint

Think about how searching in a phone book is faster when names are sorted alphabetically.

Indexes create a sorted structure that helps the database quickly locate rows without scanning the entire table, improving query speed.

📋 Factual

intermediate

Cost-Based vs Rule-Based Optimization

Which statement correctly distinguishes cost-based query optimization from rule-based optimization?

A Cost-based optimization uses statistics to estimate query costs, while rule-based relies on fixed heuristics.

B Rule-based optimization uses statistics, while cost-based uses fixed rules.

C Both cost-based and rule-based optimization ignore query execution costs.

D Cost-based optimization always chooses the longest execution plan.

💡 Hint

Consider which method uses data about the database to make decisions.

Cost-based optimization evaluates different query plans using statistics to pick the cheapest, while rule-based uses predefined rules without cost evaluation.

🔍 Analysis

advanced

Analyzing Query Execution Plans

Given a query execution plan showing a full table scan and an index scan, which strategy is generally more efficient and why?

A Full table scan is more efficient because indexes slow down queries.

B Full table scan is more efficient because it reads all rows sequentially, which is always faster.

C Both scans have the same efficiency regardless of data size.

D Index scan is more efficient because it reads fewer rows by using the index to locate data quickly.

💡 Hint

Think about how looking up a word in a dictionary compares to reading every page.

An index scan uses the index to quickly find relevant rows, reducing the number of rows read compared to a full table scan, which reads every row.

❓ Comparison

advanced

Comparing Join Strategies in Query Optimization

Which join strategy is best suited for joining a small table with a large table when the small table fits in memory?

A Cartesian join, because it multiplies all rows from both tables.

B Hash join, because it always scans both tables fully regardless of size.

C Nested loop join, because it efficiently uses the small table in memory to find matching rows.

D Merge join, because it requires both tables to be sorted and large.

💡 Hint

Consider which join uses one table repeatedly to find matches in the other.

Nested loop join is efficient when the smaller table fits in memory, allowing quick lookups for each row in the larger table.

❓ Reasoning

expert

Impact of Statistics on Query Optimization

What is the likely effect on query optimization if the database statistics are outdated or missing?

A The optimizer will always pick the fastest plan regardless of statistics.

B The optimizer may choose inefficient query plans, leading to slower query execution.

C The database will refuse to execute queries without up-to-date statistics.

D Query results will be incorrect due to missing statistics.

💡 Hint

Think about how a GPS might give a bad route if the map is outdated.

Without accurate statistics, the optimizer cannot estimate costs well and may pick slow query plans, but query results remain correct.