Query Processing and Optimization - algorithms for query processing, query optimization - Question Bank

1. What is the main drawback of exhaustive query optimization?
A) It may not find the optimal plan
B) It can be computationally very expensive and time-consuming for complex queries
C) It requires a simple query structure
D) It cannot handle joins effectively
2. Which of the following is a common type of join optimization?
A) Early selection
B) Predicate pushdown
C) Choosing the best join algorithm
D) View materialization
3. What is 'cost-based optimization'?
A) To apply a fixed set of transformation rules
B) To estimate the cost of various query plans and select the one with the lowest estimated cost
C) To minimize the number of lines of code in the query
D) To prioritize user experience over performance
4. What is the goal of 'rule-based optimization'?
A) To find the absolutely cheapest plan using a cost model
B) To apply a set of predefined transformation rules to rewrite the query into an equivalent but more efficient form
C) To execute the query using the most complex algorithm
D) To ignore all available indexes
5. What is 'predicate pushdown'?
A) Pushing a selection predicate down to an earlier stage in the query plan, ideally to the data source
B) Pushing a join predicate up to the top of the query plan
C) Pushing projection operations down
D) Pushing aggregation operations down
6. Which of these statistics are crucial for estimating the cost of a selection operation?
A) Number of distinct values in the column
B) Number of tuples in the table
C) Selectivity of the condition
D) All of the above
7. What is the role of statistics in query optimization?
A) To store user login information
B) To provide information about the data distribution and size of tables and indexes, used for cost estimation
C) To record query execution times
D) To manage database schema definitions
8. What is 'view materialization' in query optimization?
A) Creating a virtual table definition
B) Storing the results of a view query to avoid recomputing it
C) Deleting a view from the database
D) Translating a view into SQL
9. Which of the following is an example of a 'physical' optimization choice?
A) Choosing between different join algorithms (e.g., Hash Join vs. Sort-Merge Join)
B) Rewriting a query using different relational algebra operators
C) Decomposing a complex query into simpler ones
D) Eliminating redundant subqueries
10. What is a 'query block' or 'subquery' in the context of optimization?
A) A single SQL statement
B) A part of a larger query that can be optimized independently
C) The final result set
D) A temporary table
11. Why is it generally better to apply selection operations as early as possible in a query plan?
A) To increase the number of rows processed
B) To reduce the number of rows that need to be processed by subsequent operations
C) To guarantee a unique result set
D) To speed up data insertion
12. Consider the relational algebra expression (R JOIN S) WHERE condition. Which is generally a better optimization strategy?
A) Perform the JOIN first, then the WHERE
B) Perform the WHERE first, then the JOIN
C) Perform the JOIN and WHERE simultaneously
D) There is no significant difference
13. What is 'query pipelining'?
A) Executing operations one after another sequentially
B) Allowing the output of one operation to be consumed by the next operation without waiting for the first to complete entirely
C) Running multiple queries simultaneously
D) Storing query results for future use
14. Which optimization technique involves executing parts of a query in parallel across multiple processors?
A) Sequential Execution
B) Query Pipelining
C) Parallel Query Execution
D) Batch Processing
15. What is a 'semi-join' operation used for in distributed query processing?
A) To combine all rows from two tables
B) To reduce the size of one relation by sending only the necessary rows to another site for join processing
C) To perform a full table scan
D) To select specific columns
16. Which of the following is a common challenge in distributed query optimization?
A) Lack of indexes
B) High network latency and bandwidth limitations
C) Single point of failure
D) Limited CPU power
17. What is 'data localization' in the context of distributed query processing?
A) Ensuring all data resides on a single server
B) Minimizing data transfer between sites by processing data close to where it is stored
C) Encrypting data for security
D) Compressing data before transmission
18. What is 'query decomposition' in query optimization?
A) Breaking down a complex query into simpler sub-queries or operations
B) Executing a query in parallel
C) Combining results from multiple tables
D) Estimating the cost of a query
19. When would a Hash Index be more beneficial than a B+ Tree Index?
A) For range queries and ordered retrieval
B) For queries involving equality comparisons on the indexed attribute
C) When the data is frequently updated
D) When disk I/O needs to be minimized for sequential scans
20. What is the primary advantage of using a B+ Tree index for joins?
A) It allows for very fast random access only
B) It supports efficient ordered traversal, which can be used for sort-merge joins
C) It is only suitable for equality comparisons
D) It requires all data to be in memory
21. Which type of index is commonly used in relational databases for efficient range queries and ordered retrieval?
A) Hash Index
B) B+ Tree Index
C) Bitmap Index
D) Full-Text Index
22. What is an 'index' in a database context?
A) A table containing only primary keys
B) A data structure that improves the speed of data retrieval operations on a database table
C) A backup copy of the database
D) A log of all database transactions
23. Which of the following is a common data structure used to speed up selections and joins?
A) Linked List
B) Hash Table
C) B-Tree
D) Stack
24. What is a 'Cartesian Product' (or Cross Join)?
A) Combines rows from two tables based on a join condition
B) Combines every row from the first table with every row from the second table
C) Selects rows based on a filter
D) Projects specific columns
25. Consider a query 'SELECT Name, Age FROM Students;'. Which operation does this primarily represent?
A) Selection
B) Projection
C) Join
D) Cartesian Product
26. Consider a query 'SELECT * FROM Students WHERE GPA > 3.5;'. Which operation does this primarily represent?
A) Join
B) Projection
C) Selection
D) Aggregation
27. What is the purpose of the 'selection' operation in relational algebra?
A) To combine rows from two tables
B) To filter rows based on a condition
C) To remove duplicate rows
D) To select specific columns
28. Which operation is used to reduce the number of columns in a query result?
A) Selection
B) Projection
C) Join
D) Union
29. What is the 'probe' phase in Hash Join?
A) Building the hash table on the first relation
B) Using the hash table built on one relation to find matches in the other relation
C) Sorting the relations
D) Performing a full table scan
30. In Hash Join, what is the 'build' phase?
A) Probing the hash table with tuples from the second relation
B) Scanning the second relation
C) Creating a hash table on the smaller relation
D) Merging the results
31. What is the main advantage of Hash Join?
A) It requires the relations to be sorted
B) It can be very efficient for large relations, especially with sufficient memory
C) It is simple to implement
D) It guarantees an optimal plan
32. Which join algorithm is typically efficient when one relation is significantly smaller than the other?
A) Sort-Merge Join
B) Hash Join
C) Nested Loop Join
D) Index Nested Loop Join
33. When is Nested Loop Join most efficient?
A) When both relations are very large
B) When one relation is small and the other is indexed on the join attribute
C) When both relations are sorted on the join attribute
D) When memory is abundant
34. What is the basic idea behind the Nested Loop Join algorithm?
A) Build a hash table on one relation and probe with the other
B) Sort both relations and then merge them
C) For each row in the outer relation, scan the entire inner relation
D) Process blocks of tuples instead of single tuples
35. Which join algorithm is generally efficient when both relations are sorted on the join attribute?
A) Nested Loop Join
B) Block Nested Loop Join
C) Sort-Merge Join
D) Hash Join
36. What does a 'join' operation typically do?
A) Selects rows based on a condition
B) Combines rows from two or more tables based on a related column
C) Removes duplicate rows
D) Projects columns from a table
37. Which of the following is a common operation in relational algebra used in query processing?
A) Insert
B) Update
C) Join
D) Delete
38. In dynamic programming for query optimization, what is the principle of optimality?
A) An optimal plan for a query is composed of optimal sub-plans
B) The fastest plan is always the cheapest
C) All plans must be evaluated exhaustively
D) The query must be simple to be optimal
39. What is the 'System R' style of query optimization?
A) A top-down approach using dynamic programming
B) A bottom-up approach that builds plans from sub-plans
C) A greedy approach that makes locally optimal choices
D) A brute-force approach checking all possible plans
40. Which of the following is a common algorithm used in query optimization?
A) Dijkstra's Algorithm
B) Dynamic Programming
C) Quick Sort
D) Binary Search
41. What is a 'cost model' used for in query optimization?
A) To determine the user's access privileges
B) To estimate the resource consumption (e.g., I/O, CPU) of different query plans
C) To measure the network latency
D) To evaluate the accuracy of the query results
42. Which type of query optimization aims to find the absolute best execution plan, even if it takes a long time?
A) Heuristic Optimization
B) System R Style Optimization
C) Exhaustive Optimization
D) Dynamic Programming Optimization
43. What is a 'heuristic' in the context of query optimization?
A) An exact calculation of the best plan
B) A rule of thumb or approximation to find a good plan quickly
C) A definitive proof of query correctness
D) A method to verify data integrity
44. Which of the following is a key factor considered during query optimization?
A) The number of users accessing the database
B) The size of the database and available indexes
C) The operating system version
D) The programming language used to write the query
45. What is the primary function of the query optimization phase?
A) To execute the query and retrieve data
B) To check the query syntax
C) To find the most efficient execution plan for a given query
D) To store the query results
46. What is the output of the query parsing and translation phase?
A) The final query result
B) An optimized execution plan
C) An intermediate representation like a relational algebra tree or query graph
D) Raw data from the tables
47. Which component is responsible for transforming a high-level query language statement into an internal representation?
A) Query Optimizer
B) Query Executor
C) Parser
D) Relational Algebra Translator
48. What is the purpose of the parsing phase in query processing?
A) To generate an execution plan
B) To check for syntax errors and translate SQL into a relational algebra expression
C) To estimate the cost of different execution plans
D) To perform the actual data retrieval
49. Which of the following is NOT a phase of query processing?
A) Parsing and Translation
B) Optimization
C) Execution
D) Data Entry
50. What is the primary goal of query processing?
A) To minimize disk I/O operations
B) To minimize CPU usage
C) To retrieve data as quickly as possible
D) To reduce network traffic
All Question Banks
E-Book Query Processing and Optimization - algorithms for query processing, query optimization One Line Questions Query Processing and Optimization - algorithms for query processing, query optimization Online Test Query Processing and Optimization - algorithms for query processing, query optimization