Add harmless projections to remove unused attributes year γ maxbirthday

Add harmless projections to remove unused attributes

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
Image of page 51
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
Image of page 52
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
Image of page 53
Outline - Query Processing Relational algebra level transformations good transformations Detailed query plan level estimate costs generate and compare plans 54 / 106
Image of page 54
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
Image of page 55
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
Image of page 56
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
Image of page 57
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
Image of page 58
Important fact: The size (# tuples) of the result set of a relational operator is not dependent on the implementation algorithm.
Image of page 59
Image of page 60

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

  • Left Quote Icon

    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.

    Student Picture

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

  • Left Quote Icon

    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.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    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.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask ( soon) You can ask (will expire )
Answers in as fast as 15 minutes
A+ icon
Ask Expert Tutors