Database Query Optimization: Techniques and Algorithms, Study notes of Principles of Database Management

Download Database Query Optimization: Techniques and Algorithms and more Study notes Principles of Database Management in PDF only on Docsity!

CMSC 424 – Database design

Lecture 18

Query optimization

Mihai Pop

Admin

• (^) Homework 3 due

Cost-Based Optimization

• Consider finding the best join-order for r 1 r 2 ... r n

• (^) There are (2( n – 1))!/( n – 1)! different join orders for above expression. With n = 7, the number is 665280, with n = 10, the number is greater than 176 billion!
• (^) No need to generate all the join orders. Using dynamic programming, the least-cost join order for any subset of { r 1 , r 2 ,... r n } is computed only once and stored for future use.

Dynamic Programming in Optimization

• (^) To find best join tree for a set of n relations:
  • (^) To find best plan for a set S of n relations, consider all possible plans of the form: S 1

( S – S

1 ) where S 1 is any non-empty subset of S.

• (^) Recursively compute costs for joining subsets of S to find the cost of each plan. Choose the cheapest of the 2 n - 1 alternatives.
• (^) Base case for recursion: single relation access plan
  • Apply all selections on R i using best choice of indices on R i
• (^) When plan for any subset is computed, store it and reuse it when it is required again, instead of recomputing it - (^) Dynamic programming

Dynamic programming example

• (^) Enumerate all equivalent expressions for: A ⋈ B ⋈ C ⋈ D ⋈ E A ⋈ (B ⋈ C ⋈ D ⋈ E) A ⋈ (B ⋈ (C ⋈ D ⋈ E)) A ⋈ (B ⋈ (C ⋈ (D ⋈ E))) remember the best of two ways to A ⋈ (B ⋈ (C ⋈ (E ⋈ D))) represent D ⋈ E A ⋈ (B ⋈ ((D ⋈ E)⋈ C)) here we can use the precomputed expressions for D ⋈ E and store the best of different ways to represent C ⋈ D ⋈ E

Left Deep Join Trees

• (^) In left-deep join trees, the right-hand-side input for each join is a relation, not the result of an intermediate join.

Interesting Sort Orders

• Consider the expression ( r 1 r 2 ) r 3 (with A as common attribute)
• (^) An interesting sort order is a particular sort order of tuples that could be useful for a later operation - Using merge-join to compute^ r 1 r 2 may be costlier than hash join but generates result sorted on A - Which in turn may make merge-join with^ r 3 cheaper, which may reduce cost of join with r 3 and minimizing overall cost - (^) Sort order may also be useful for order by and for grouping
• (^) Not sufficient to find the best join order for each subset of the set of n given relations - (^) must find the best join order for each subset, for each interesting sort order - (^) Simple extension of earlier dynamic programming algorithms - (^) Usually, number of interesting orders is quite small and doesn’t affect time/space complexity significantly

Heuristic Optimization

• (^) Cost-based optimization is expensive, even with dynamic programming.
• (^) Systems may use heuristics to reduce the number of choices that must be made in a cost-based fashion.
• (^) Heuristic optimization transforms the query-tree by using a set of rules that typically (but not in all cases) improve execution performance: - (^) Perform selection early (reduces the number of tuples) - (^) Perform projection early (reduces the number of attributes) - (^) Perform most restrictive selection and join operations (i.e. with smallest result size) before other similar operations. - (^) Some systems use only heuristics, others combine heuristics with partial cost-based optimization.

Structure of Query Optimizers (Cont.)

• (^) Some query optimizers integrate heuristic selection and the generation of alternative access plans. - (^) Frequently used approach - (^) heuristic rewriting of nested block structure and aggregation - (^) followed by cost-based join-order optimization for each block - (^) Some optimizers (e.g. SQL Server) apply transformations to entire query and do not depend on block structure
• (^) Even with the use of heuristics, cost-based query optimization imposes a substantial overhead. - (^) But is worth it for expensive queries - (^) Optimizers often use simple heuristics for very cheap queries, and perform exhaustive enumeration for more expensive queries

Optimizing Nested Subqueries**

• (^) Nested query example: select customer_name from borrower where exists ( select * from depositor where depositor.customer_name = borrower.customer_name )
• (^) SQL conceptually treats nested subqueries in the where clause as functions that take parameters and return a single value or set of values - (^) Parameters are variables from outer level query that are used in the nested subquery; such variables are called correlation variables

Optimizing Nested Subqueries (Cont.)

• (^) E.g.: earlier nested query can be rewritten as select customer_name from borrower, depositor where depositor.customer_name = borrower.customer_name - (^) Note: the two queries generate different numbers of duplicates (why?) - (^) Borrower can have duplicate customer-names - (^) Can be modified to handle duplicates correctly as we will see
• (^) In general, it is not possible/straightforward to move the entire nested subquery from clause into the outer level query from clause - (^) A temporary relation is created instead, and used in body of outer level query

Optimizing Nested Subqueries (Cont.)

In general, SQL queries of the form below can be rewritten as shown

• (^) Rewrite: select … from L 1 where P 1 and exists ( select * from L 2 where P 2 )
• To: create table t 1 as select distinct V from L 2 where P 2 1 select … from L 1 , t 1 where P 1 and P 2 2
• P 2 1 contains predicates in P 2 that do not involve any correlation variables
• P 2 2 reintroduces predicates involving correlation variables, with relations renamed appropriately
• (^) V contains all attributes used in predicates with correlation variables

Optimizing nested subqueries

• (^) Decorrelation is more complicated when
  • (^) the nested subquery uses aggregation, or
  • (^) when the result of the nested subquery is used to test for equality, or
  • (^) when the condition linking the nested subquery to the other query is not exists ,
  • (^) and so on.

Materialized Views**

• (^) A materialized view is a view whose contents are computed and stored.
• (^) Consider the view c reate view branch_total_loan ( branch_name, total_loan ) as select branch_name , sum ( amount ) from loan group by branch_name
• (^) Materializing the above view would be very useful if the total loan amount is required frequently - (^) Saves the effort of finding multiple tuples and adding up their amounts