Add harmless projections to remove unused attributes year γ maxbirthday

# Add harmless projections to remove unused attributes

• Notes
• 106

This preview shows page 51 - 60 out of 106 pages.

Add harmless projections to remove unused attributes year γ max(birthday) π year,birthday 1 name=starName MovieStar StarsIn year γ max(birthday) π year,birthday 1 name=starName π name,birthday π year,starName MovieStar StarsIn 51 / 106
Canonical Logical Query Trees A canonical logical query tree (Grouping Operators) is a logical query tree where all associative and commutative operators with more than two operands are converted into multi-operand operators. i.e., group nodes that have the same operator into one node with many children This makes it more convenient and obvious that the operands can be combined in any order. This is especially important for joins as the order of joins may make a significant difference in the performance of the query. Original Query Tree 1 1 1 R S T U Canonical Query Tree 1 R S T U 52 / 106
Summary No transformation is always good at the LQP level Selections push down tree as far as possible if condition is an AND, split and push separately sometimes need to push up before pushing down Projections can be pushed down new ones can be added (but be careful) Duplicate elimination sometimes can be removed Selection/product combinations can sometimes be replaced with join Many transformations lead to “promising” plans 53 / 106
Outline - Query Processing Relational algebra level transformations good transformations Detailed query plan level estimate costs generate and compare plans 54 / 106
Evaluating Logical Query Plans The transformations discussed so far intuitively seem like good ideas But how can we evaluate them more scientifically? Estimate size of relations, also helpful in evaluating physical query plans 55 / 106
Steps in query optimization 1. We start with an initial logical query plan (obtained by transforming the parse tree into a relational algebra tree) 2. We transform this initial logical query plan into optimal logical query plan using Algebraic Laws 3. We choose the best feasible algorithm for each relational operator in the optimal logical query plan to obtain the optimal physical query plan 56 / 106
Comparing different logical query plans Before we can improve a query plan, we must have a measure to determine the cost of the logical query plans Measuring the cost of logical query plans 1. The ultimate cost measure is the execution time (#disk IOs performed) of the query plan However, Execution time is a measure used for implementation algorithms i.e.: the physical query plan We are comparing different logical query plan 2. A good approximation of the execution time (# disk IOs) measure is the size (# tuples) of the result produced by the operations 57 / 106
Which query plan is better? The answer to the question is determined by: The size (# tuples) of the intermediate result relations produced by each l ogical query plan Because, the size (# tuples) will determine the number of disk IO performed by the relational operators (algorithms) further up in the query tree We need a method to compute (estimate) the size of the intermediate results of the relational operators on the logical query plan 58 / 106
Important fact: The size (# tuples) of the result set of a relational operator is not dependent on the implementation algorithm.

#### You've reached the end of your free preview.

Want to read all 106 pages?

• Fall '19
• Joseph Rosen
• Relational model, StarsIn, R 1c S

### 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