Why query optimization reduces execution time in DBMS Theory - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does query optimization improve performance?

Which of the following best explains why query optimization reduces execution time in a database?

A It increases the number of rows scanned to ensure accuracy.

B It rearranges the query to use indexes and efficient join methods, reducing the amount of data processed.

C It duplicates data to speed up retrieval.

D It disables caching to force fresh data reads.

💡 Hint

Think about how databases avoid unnecessary work.

Query optimization finds the best way to run a query by using indexes and efficient joins, which reduces the data scanned and speeds up execution.

📋 Factual

intermediate

What is a common technique used in query optimization?

Which technique is commonly used by query optimizers to reduce execution time?

A Using indexes to quickly locate rows

B Full table scan of all tables involved

C Ignoring WHERE clauses to speed up processing

D Duplicating tables to avoid joins

💡 Hint

Think about how databases find data quickly.

Indexes allow the database to find rows quickly without scanning entire tables, which reduces execution time.

🔍 Analysis

advanced

Analyzing the effect of join order on execution time

Consider a query joining three tables: A, B, and C. How does changing the join order affect execution time?

A Join order has no effect on execution time.

B Join order only affects the output, not performance.

C Changing join order always increases execution time.

D Changing join order can reduce intermediate result size, speeding up the query.

💡 Hint

Think about how intermediate results affect processing.

Choosing the right join order can minimize the size of intermediate results, reducing the amount of data processed and speeding up execution.

❓ Comparison

advanced

Comparing execution plans with and without optimization

Which statement correctly compares execution plans of a query with and without optimization?

A Optimized plans use indexes and fewer scans; unoptimized plans often use full scans.

B Both plans always use the same indexes and scans.

C Optimized plans ignore indexes to speed up execution.

D Unoptimized plans use indexes more efficiently than optimized plans.

💡 Hint

Consider how optimization changes data access methods.

Optimized plans typically use indexes and avoid full table scans, reducing data processed and execution time.

❓ Reasoning

expert

Why does reducing disk I/O lower query execution time?

Why does query optimization focus on reducing disk input/output (I/O) operations to lower execution time?

A Reducing disk I/O increases network traffic, which speeds up queries.

B Disk I/O is faster than CPU processing, so more I/O improves speed.

C Disk I/O is slower than memory operations, so reducing it speeds up queries.

D Disk I/O has no impact on query execution time.

💡 Hint

Think about the speed difference between disk and memory.

Disk operations are slower than memory operations. Optimizers reduce disk I/O to avoid waiting for slow data reads, thus lowering execution time.