Q.1 What is the main goal of query optimization in databases?
To increase query execution time
To reduce query execution cost
To create new tables
To delete redundant data
Explanation - Query optimization aims to choose the most efficient query execution plan to minimize resource usage and response time.
Correct answer is: To reduce query execution cost
Q.2 Which of the following is a common step in query processing?
Parsing
Compilation of programs
Index creation
Backup scheduling
Explanation - Query processing involves parsing the query, validating syntax, generating execution plans, and then executing the query.
Correct answer is: Parsing
Q.3 What does a query execution plan represent?
A set of database indexes
The sequence of operations to execute a query
The table schema
The network configuration
Explanation - A query execution plan details the operations (like joins, scans) and their order for executing a query efficiently.
Correct answer is: The sequence of operations to execute a query
Q.4 Which operator is typically the most expensive in query execution?
Projection
Selection
Join
Rename
Explanation - Join operations often involve combining large tables, making them costlier compared to other basic relational operations.
Correct answer is: Join
Q.5 Which technique helps in reducing the number of tuples processed in query optimization?
Selection pushdown
Materialized views
Indexing
Schema normalization
Explanation - Selection pushdown applies selection conditions as early as possible to reduce the number of tuples processed by subsequent operations.
Correct answer is: Selection pushdown
Q.6 Which of the following is a cost-based query optimization approach?
Heuristic optimization
Greedy algorithm
Dynamic programming
Tree traversal
Explanation - Dynamic programming estimates costs of alternative query execution plans and chooses the least expensive one.
Correct answer is: Dynamic programming
Q.7 What is the purpose of relational algebra in query optimization?
To define data storage formats
To provide a formal framework for query transformations
To create indexes
To design network protocols
Explanation - Relational algebra provides operators that allow queries to be rewritten and optimized systematically.
Correct answer is: To provide a formal framework for query transformations
Q.8 What is a heuristic in query optimization?
A guaranteed method to find the best plan
A set of rules to transform queries for efficiency
A method for backup
A type of index structure
Explanation - Heuristic optimization uses rules like pushing selections and projections to reduce cost without evaluating all possible plans.
Correct answer is: A set of rules to transform queries for efficiency
Q.9 Which of these join algorithms is best for small tables?
Nested loop join
Hash join
Merge join
Sort-merge join
Explanation - Nested loop joins are simple and efficient for small tables but inefficient for large ones.
Correct answer is: Nested loop join
Q.10 What is cardinality estimation in query optimization?
Estimating table size in bytes
Estimating the number of tuples resulting from an operation
Counting the number of queries
Predicting disk failures
Explanation - Cardinality estimation helps the optimizer predict costs and choose efficient execution plans.
Correct answer is: Estimating the number of tuples resulting from an operation
Q.11 What does a parser do in query processing?
Validates syntax and creates a parse tree
Executes the query
Optimizes joins
Manages transactions
Explanation - The parser checks query syntax and constructs a parse tree for further processing.
Correct answer is: Validates syntax and creates a parse tree
Q.12 Which database object helps the optimizer quickly locate data?
View
Trigger
Index
Stored procedure
Explanation - Indexes allow faster retrieval of rows based on key values, aiding the optimizer in plan selection.
Correct answer is: Index
Q.13 Which of the following is NOT part of query processing?
Parsing
Optimization
Execution
Data replication
Explanation - Query processing involves parsing, optimization, and execution, but replication is a separate database operation.
Correct answer is: Data replication
Q.14 Which of the following strategies reduces the size of intermediate results?
Projection pushdown
Index creation
Caching
Replication
Explanation - Projection pushdown removes unnecessary columns early, reducing intermediate result sizes and improving performance.
Correct answer is: Projection pushdown
Q.15 Which join algorithm is efficient when both inputs are sorted?
Nested loop join
Merge join
Hash join
Cartesian join
Explanation - Merge join works efficiently on sorted inputs by merging tuples in order.
Correct answer is: Merge join
Q.16 What is the effect of a bad query plan?
Faster query execution
Increased resource usage and slower queries
More storage space
Automatic backup creation
Explanation - A poorly chosen execution plan can lead to longer execution times and inefficient use of CPU, memory, and I/O.
Correct answer is: Increased resource usage and slower queries
Q.17 Which of the following is a type of access path for query execution?
Table scan
Data replication
View materialization
Transaction log
Explanation - An access path defines how data is retrieved, such as scanning the entire table or using an index.
Correct answer is: Table scan
Q.18 Which factor does NOT affect query optimization?
Table statistics
Available indexes
Network bandwidth
Query syntax
Explanation - Query optimization depends on statistics, indexes, and query structure. Network bandwidth is irrelevant for local query plan selection.
Correct answer is: Network bandwidth
Q.19 What is a materialized view used for in query optimization?
To store precomputed query results for faster access
To define table schemas
To execute triggers
To replicate data across databases
Explanation - Materialized views cache query results to speed up repeated queries.
Correct answer is: To store precomputed query results for faster access
Q.20 Which of the following is a rule-based optimization technique?
Pushing selections before joins
Dynamic programming
Genetic algorithms
Cost estimation
Explanation - Rule-based optimization applies transformation rules to improve query efficiency without considering actual costs.
Correct answer is: Pushing selections before joins
Q.21 Which type of join is preferred when one table fits in memory?
Nested loop join
Hash join
Merge join
Cartesian join
Explanation - Hash join is efficient when one table fits in memory because it hashes one table and probes the other for matches.
Correct answer is: Hash join
Q.22 Which operation in relational algebra is usually performed first for optimization?
Join
Selection
Projection
Union
Explanation - Selections are pushed down early to reduce the number of tuples processed by subsequent operations.
Correct answer is: Selection
Q.23 Which of the following helps the optimizer choose the best plan?
Database statistics
Stored procedures
Triggers
User roles
Explanation - Statistics like table size, value distributions, and index availability guide the optimizer in selecting the most efficient execution plan.
Correct answer is: Database statistics
Q.24 What is the main difference between heuristic and cost-based optimization?
Heuristic uses rules, cost-based uses estimated costs
Heuristic creates indexes, cost-based drops indexes
Heuristic executes queries, cost-based parses queries
Heuristic only works on joins, cost-based only on selections
Explanation - Heuristic optimization applies fixed rules for efficiency, while cost-based optimization evaluates multiple plans using cost estimates.
Correct answer is: Heuristic uses rules, cost-based uses estimated costs