Which component of a DBMS is responsible for evaluating different query execution strategies and selecting the best one?

Which component of a DBMS is responsible for evaluating different query execution strategies and selecting the best one?

• A. operational information
• B. checkpoint
• C. query optimizer
• D. complete mirrored backup

Answer: (C)

Deciding how to execute a query efficiently is the concept being tested. The component that evaluates different query execution strategies and selects the best one is the query optimizer. It analyzes possible ways to carry out the query—such as which join order to use, whether to scan a table or use an index, and which access paths to take for each table. Using statistics about the data (like table sizes, distribution, and index availability) and a cost model, it estimates the resources (I/O, CPU, memory) each plan would consume and picks the plan with the lowest estimated cost to pass to the execution engine. Operational information is just data about the current operations and system state, not about choosing how to run a query. A checkpoint is a recovery mechanism that periodically saves the database state to enable restart after a crash. A complete mirrored backup is a full duplicate copy of data kept for disaster recovery. These features don’t decide how to execute a query; the optimizer does.

Deciding how to execute a query efficiently is the concept being tested. The component that evaluates different query execution strategies and selects the best one is the query optimizer. It analyzes possible ways to carry out the query—such as which join order to use, whether to scan a table or use an index, and which access paths to take for each table. Using statistics about the data (like table sizes, distribution, and index availability) and a cost model, it estimates the resources (I/O, CPU, memory) each plan would consume and picks the plan with the lowest estimated cost to pass to the execution engine.

Operational information is just data about the current operations and system state, not about choosing how to run a query. A checkpoint is a recovery mechanism that periodically saves the database state to enable restart after a crash. A complete mirrored backup is a full duplicate copy of data kept for disaster recovery. These features don’t decide how to execute a query; the optimizer does.