Final - Query Optimization Flashcards
What are the steps involved in query processing?
A: A SQL query is scanned, parsed, and validated, producing an immediate form (relational algebra).
What is a query plan?
A: An execution strategy for a query before execution.
: What is the goal of query optimization?
A: To find an efficient final query plan for execution.
What is a query tree?
A tree data structure representing a relational algebra expression with base relations as leaf nodes and relational operators as internal nodes.
What are the steps for transforming a query into its canonical form?
Combine relations with cartesian products.
Perform one SELECT operation (includes join conditions).
Perform one PROJECT operation.
What is a query graph?
A: A graph-based representation of a query that does not specify the order of execution.
What is heuristic query optimization?
Optimization using heuristic rules to find an efficient execution plan without guaranteeing the most optimal one.
What are key heuristic rules for query optimization?
Perform SELECT and PROJECT early to reduce data size.
Execute the most restrictive SELECT and JOIN operations first.
Convert Cartesian products followed by SELECTs into JOINs.
What is an equivalence rule in query optimization?
A: A rule that allows replacing one relational algebra expression with an equivalent one.
Q: What is join ordering in query optimization?
A: The process of determining the best order to join multiple relations in a query.
Q: What is join associativity?
A: The property that allows reordering joins: (r1 ⋈ r2) ⋈ r3 = r1 ⋈ (r2 ⋈ r3).
Q: What is cost-based query optimization?
An optimization technique that estimates and compares the costs of different execution plans to choose the cheapest one.
How is plan cost estimated in cost-based query optimization?
Using statistics about relations, estimated statistics for intermediate results, and cost functions for algorithms.
