16.2.Algebraic Laws for Improving Query Plans

1. 16.2.Algebraic Laws for Improving Query Plans

2. 16.2 Algebraic Laws for Improving Query Plans • 16.2.1 Commutative and Associative Laws • 16.2.2 Laws Involving Selection • 16.2.3 Pushing Selections • 16.2.4 Laws Involving Projection • 16.2.5 Laws About Joins and Products • 16.2.6 Laws Involving Duplicate Elimination • 16.2.7 Laws Involving Grouping and Aggregation • 16.2.8 Exercises for Section 16.2

3. 16.2.4 Laws Involving Projection • Projection, like selection can be pushed down through many other operators • Pushing Projection usually involves introducing a new projection somewhere below an existing projection. • Projection differs from selection in the aspect that projection reduces the length of the tuples whereas selection reduces the number of the tuples

4. 16.2.4. Laws involving Projection • Consider term πE  x • E : attribute, or expression involving attributes and constants. • All attributes in E are input attributes of projection and x is output attribute • Simple projection: if a projection consists of only attributes. • Example: π a,b,c (R) is simple. a,b,c are input and output attributes. • Projection can be introduced anywhere in expression tree as long as it only eliminates attributes that are never used.

5. 16.2.4. Laws involving Projection (cont….) • πL(R S) = πL(πM(R) πN(S)) ; M and N are all attributes of R and S that are either join (in schema of both R and S) or input attributes of L • πL(R c S) = πL(πM(R) cπN(S)) ; M and N are all attributes of R and S that are either join(mentioned in condition of C ) or input attributes of L • πL(R x S) = πL(πM(R) x πN(S)) ; M and N are all attributes of R and S that are input attributes of L Projections cannot be pushed below set unions or either of set or bag versions of intersection or difference at all.

6. 16.2.4 Laws Involving Projection SELECT starName FROM StarsIn WHERE year = 1996 π starName σ movieYear = 1996 StarsIn Fig : Logical query plan for the above query We can introduce a projection in the above Figure

7. 16.2.4 Laws Involving Projection π starName σ movieYear = 1996 π starName, movieYear StarsIn Convert the tree into relational algebra, then simplify as much as you can

8. 16.2.5 Laws About Joins and Products • RCS= C(R S) • RS= L (C(R S)) Where C is the condition that equates each pair of atrribute from R and S with the same name, and L is the list that includes one attribute from each equted attributed and all other attributes of R and S.

9. 16.2.6 Laws Involving Duplicate Elimination • The operator δ , which eliminates duplicates from a bag can be pushed through only some of the operators • Moving δ down the tree reduces the size of intermediate relation and may therefore be beneficial • In some cases, we can move δ to a position where it can be eliminated because it is applied to a relation that does not have any duplicates

10. 16.2.6 Laws Involving Duplicate Elimination • δ( R ) = R if R has no duplicates Important cases of such a relation R include • A stored relation with a declared primary key • A relation that is the result of a γ operation ,since grouping creates a relation with no duplicates • δ cannot be moved across the operators like U , - , π.

11. 16.2.6 Laws involving duplicate elimination Laws that “push” δ (delta) through other operator • δ(R x S) = δ(R) x δ(S) • δ(R S) = δ(R) δ(S) • δ(R c S) = δ(R) c δ(S) • δ( c(R)) = c(δ(R)) δ eliminates duplicates from a bag, but cannot be pushed through all the operators

12. 16.2.7 Laws Involving Grouping and Aggregation • While using grouping and aggregation ,the applicability of many transformation depends on the details of the aggregation used. • Due to the above ,we cannot state laws in generality. • One exception is the law below that γ absorbs δ δ(γL(R)) = γL ( R )

13. 16.2.7 Laws Involving Grouping and Aggregation • We may project useless attributes prior to applying γ operation • γL ( R ) = γL(πM (R ) where M is the list containing at least all those attributes of R that are mentioned in L.

14. Laws involving grouping and aggregation (cont…) • Some aggregations like MIN and MAX are not affected by presence or absence of duplicates • Others like SUM,COUNT,AVG produce different values if duplicates are eliminated prior to aggregation.

15. 16.2.7 Laws Involving Grouping and Aggregation • Suppose we have the relation • MovieStar(name ,addr ,gender ,birthdate) • StarsIn(movieTitle ,movieYear ,starName) • Consider the query below • Select movieYear ,MAX(birthDate) • FROM MovieStar ,StarsIn • WHERE name = starName • GROUP BY movieYear

16. 16.2.7 Laws Involving Grouping and Aggregation • The FROM list is expressed by a product and the WHERE clause by a selection above it. • The grouping and aggregation are expressed by the γ. • Combine the selection and product into an equijoin • Generate a δ below the γ ,since the γ is duplicate-impervious • Generate a π between the γ and the introduced δ to project onto movieYear and birthDate ,the only attributes relevant to the γ

17. 16.2.7 Laws Involving Grouping and Aggregation γmovieYear ,MAX(birthDate) σname = starName  MovieStar StarsIn 1. Use 16.2.5. (and following 2 reasons) we can rewrite the tree 2. There is no duplication in output (because γ), we can add  3. By projection law We can add .

18. 16.2.7 Laws Involving Grouping and Aggregation γmovieYear ,MAX(birthDate) movieYear ,birthDate δ name = starName MovieStar StarsIn Figure : Second query plan

19. 16.2.7 Laws Involving Grouping and Aggregation γmovieYear ,MAX(birthDate) πmovieYear ,birthDate name = starName δ δ πbirthDate,name πbirthDate,name MovieStar StarsIn Figure : Third query plan  can be push down

20. 16.2.7a Additional Example • From DB1

21. 16.2.7a Laws Involving Grouping and Aggregation • SELECT PNUM, SUM(QTY) • FROM SHIPMENTs, Parts • GROUP BY PNAME;

22. 16.2.7b Laws Involving Grouping and Aggregation γpname ,SUM(qty) sum σpnum = pnum  Shipments (Sh) Parts(P) Figure : Initial Logical query plan for the query γpname ,SUM(qty)  sum(σsh.pnum=p.pnum (ShipmentsParts))

23. 16.2.7c Laws Involving Grouping and Aggregation γpname ,SUM(qty)sum pname ,QTY δ Shipments Parts γpname ,SUM(qty)sum ( pname.qty (Shipments Parts))

24. 16.2.7d Laws Involving Grouping and Aggregation γpname ,SUM(qty)sum πpname ,qty δ δ πqty,pnum pnum, pname Shipments Parts γpname ,Sum(qty) sum( pname.qty((  pname.qty (Shipments)) (  pname.qty (Parts))))

25. 16.2.8 Exercises for Section 16.2

26. 16.1. SQL(not RAE) Figure 16.2 • select movietitle • from starsIn • where starname in • (select name • from moviestar • where birthdate • like '%1960');

27. 16.3. SQL/RAE of Figure 16.19 • select movietitle • from starsIn a, (select name • from moviestar • where birthdate like '%1960') temp • where a.starname = temp.name; • RAE=Relational Algebra Expressible SQL

28. 16.3 Figure 16.19 γpname ,SUM(qty)sum πpname ,qty δ δ πqty,pnum pnum, pname Shipments Parts γpname ,Sum(qty) sum( pname.qty((  pname.qty (Shipments)) (  pname.qty (Parts))))

29. SQL in Figure 16.20 Select distinct m1.movieTitle, m1.movieYear From StarsIn m1 Where m1.movieYear – 40 <= ( Select AVG(birthdate) From StarsIn m2, Moviestar s Where m2.starName=s.name AND m1.movieTitle = m2.movieTitle AND m1.movieyear = m2.movieyear );

30. SQL in Figure 16.22 Select distinct m.movieTitle, m.movieYear From StarsIn m1, (Select m2.movieTitle, m2.movieyear, AVG(birthdate) as ave From StarsIn m2, Moviestar s Where m2.starName=s.name Group by m2.movieTitle, m2.movieyear ) m Where m1.movieTitle = m.movieTitle and m1.movieYear – 40 <=ave;