lec05_query_processing_2up.pdf

# 80 what about set operations reliable estimates are

• Notes
• 69

This preview shows page 43 - 46 out of 69 pages.

80 What about set operations? Reliable estimates are possible for set operations that can be rewritten as conjunctions or disjunctions: - recall σ c 1 ( R ) σ c 2 ( R ) = σ c 1 c 2 ( R ) and - σ c 1 ( R ) σ c 2 ( R ) = σ c 1 c 2 ( R ) If that’s not possible, we can at least get upper bounds on T ( Q ) : T ( R S ) = T ( R ) + T ( S ) T ( R S ) = min( T ( R ) , T ( S )) T ( R - S ) = T ( R ) 81

Subscribe to view the full document.

Cost-based Query Optimization (case study: join ordering) Goal of the Query Optimizer The goal of query optimizer is to find an optimal query plan for an input query. That is, a plan with the lowest possible cost. In general, query optimization is NP-hard. Therefore, in practice, the optimizer considers only a subset of the possible query plans for each query, estimates their cost , and picks a plan with lowest estimated cost. Rule #1 of query optimization The time it takes the optimizer to find a good plan cannot exceed the time it would take to execute a “reasonable” plan. 82
Query Optimization With Dynamic Programming (Sellinger, 1979) Dynamic programming is an algorithmic problem-solving technique suitable for optimization problems where the solution builds on solutions to sub-problems. Dynamic programming works bottom-up : decompose the problem into every atomic sub-problem and solve them; the solution with n + 1 sub-problems builds on the lowest cost solution of size n . π a 1 ,...,a n σ c 1 c 2 R S π a 1 ,...,a n σ c 1 σ c 2 R S 83 Dynamic Programming Optimizer Step 1: for each atomic sub-problem Q , compute: - T ( Q ) : the estimated size of Q - plan ( Q ) : the lowest-cost plan to compute Q - cost ( Q ) : the actual cost to compute Q . Step 2: build, recursively, all legal solutions of size n + 1 , considering all combinations of a solution of size n and the solution to another sub-problem. Keep the one with least cost. Invalid partial solutions (e.g., doing a projection “before scanning a table”) are ignored. In the following slides, we illustrate how the Sellinger optimizer would find the optimal join ordering , ignoring other operators, and assuming the only join algorithm is nested-loop-joins. 84

Subscribe to view the full document.

Why Focus on Join Ordering? There is a combinatorial number of plans that join N relations. Example: N = 4 . Here are the 5 “shapes” for the 4-way join subtree, shown with the relations sorted alphabetically : R S T U R S T U R S T U R S T U R S T U The total number of plans must take into account all permutations of the relations ( 4! = 24 ). In total, there are 5 · 24 = 120 ways to do the 4-way join 6 . 6 There are 620 ways to do a 5-way join. 85 How many actual plans? The space of possible plans considers, for every single one of the 120 ways of performing the 4-way join, all possible legal rewrites of the query (i.e., pushing selections down or not?) and, for each rewrite, all possible algorithms for each of the joins (nested-loop, hash-join, merge-join,...) and scans (table scan, index scan, if so, which one?).
You've reached the end of this preview.
• Winter '16
• Bill Murray

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern