By: Prof. Dr. Fazal Rehman | Last updated: September 23, 2024
MCQs on Query Processing and Optimization in DBMS
What is the primary goal of query optimization in a DBMS?A. To minimize the number of queries
B. To maximize the use of indexes
C. To minimize the response time and resource usage
D. To ensure data integrity
Answer: C. To minimize the response time and resource usage
Which of the following is an example of a logical optimization technique?A. Index selection
B. Query rewriting using algebraic transformations
C. Disk scheduling
D. Buffer management
Answer: B. Query rewriting using algebraic transformations
What does the term ‘cost-based optimization’ refer to in DBMS?A. Optimization based on user preferences
B. Optimization based on the structure of the query
C. Optimization based on the estimated cost of different query execution plans
D. Optimization based on the data distribution
Answer: C. Optimization based on the estimated cost of different query execution plans
Which of the following is not typically considered during physical query optimization?A. Index usage
B. Join methods
C. Query rewriting
D. Access paths
Answer: C. Query rewriting
What is a ‘query execution plan’ in the context of DBMS?A. A logical representation of the query
B. A sequence of operations that the DBMS will perform to execute the query
C. A high-level summary of the query
D. A set of user preferences for the query
Answer: B. A sequence of operations that the DBMS will perform to execute the query
Which join method is most efficient when one of the tables is very small compared to the other?A. Nested Loop Join
B. Hash Join
C. Sort-Merge Join
D. Index Nested Loop Join
Answer: A. Nested Loop Join
What does the ‘SELECTIVITY’ of a query refer to?A. The number of joins in the query
B. The fraction of rows in the table that satisfy the query’s conditions
C. The number of indexes used by the query
D. The complexity of the query’s syntax
Answer: B. The fraction of rows in the table that satisfy the query’s conditions
In query processing, what is ‘pipelining’?A. Executing multiple queries in parallel
B. Breaking down a query into smaller sub-queries
C. Passing intermediate results directly to the next operation without writing to disk
D. Scheduling queries based on their priority
Answer: C. Passing intermediate results directly to the next operation without writing to disk
Which operator is used to combine the results of two queries into a single result set in SQL?A. JOIN
B. UNION
C. INTERSECT
D. EXCEPT
Answer: B. UNION
What is the purpose of using ‘statistics’ in query optimization?A. To track user activity
B. To provide detailed information about data distribution and table usage
C. To enforce data integrity
D. To create backups of the database
Answer: B. To provide detailed information about data distribution and table usage
Which of the following is a heuristic-based optimization technique?A. Cost-based optimization
B. Rule-based optimization
C. Dynamic programming
D. Genetic algorithms
Answer: B. Rule-based optimization
What is a ‘materialized view’ in the context of query optimization?A. A view that is computed on demand
B. A view that is stored physically on disk
C. A view that combines multiple queries
D. A view that is only accessible to the database administrator
Answer: B. A view that is stored physically on disk
Which algorithm is used for optimizing complex queries with multiple joins?A. Greedy algorithm
B. Nested loop algorithm
C. Dynamic programming algorithm
D. Brute force algorithm
Answer: C. Dynamic programming algorithm
In the context of query optimization, what is ‘predicate pushdown’?A. Evaluating predicates as early as possible to reduce the size of intermediate results
B. Delaying predicate evaluation until the final result is computed
C. Applying predicates only after sorting the data
D. Ignoring predicates to speed up query execution
Answer: A. Evaluating predicates as early as possible to reduce the size of intermediate results
Which of the following is an example of a physical operator in a query execution plan?A. Selection
B. Projection
C. Sort
D. Cartesian product
Answer: C. Sort
