January 11, 2006

---

Warm-up

---

• how else could we have re-written ?
  
  
  
  

---

Question from Last Class

---

• does the distributive law of intersection over union hold for bags, e.g., , no
• the following relation instances provides a counter-example, e.g., A(a), B(a), C(a),
  
  

  A	B	C
  1	3	1
  3	4	3
  3		3

---

Pushing Selection through Relation Operators

---

• push to both arguments:
  • 
• push to left (or both):
  • 
  • 
• push , , , and to argument(s) which apply (the following assumes R contains all attributes in C)
  • 
  • 
  • 
  • 
  • when possible to push to both, doing so may or may not improve plan
• replacing lhs with rhs is one of the most powerful ideas in query optimization

---

Sometimes Helpful to Push Up First, then Down

---

• Consider relation schemas StarsIn(title, year, starName) and Movie(title, year, length, inColor, studioName, producerC#)

• CREATE VIEW Movies1996 AS
     SELECT *
     FROM Movie
     WHERE year=1996;
  

• SELECT starName, studioName
  FROM Movies1996 NATURAL_JOIN StarsIn;
  

• 
• apply in reverse
• 
• rewrite previous expression as

---

Extended Relational Algebra

---

• duplicate elimination operator
  • maps bags to sets
  • e.g.,
• extended projection operator
  • e.g.,
  • A, B, and C are input attributes
  • X is an output attribute
  • a simple projection involves no renaming or expressions other than single attributes

---

Rewrite Rules Involving Projection

---

• less helpful that pushing projections; retains all tuples
• we may prune any attributes not used by any operator at a higher level of the tree
• , M and N contain join attributes (those common to R and S) and input attributes of L, where applicable
• M and N contain join attributes and input attributes of L, where applicable
• , M and N contain input attributes of L, where applicable
• Example (courtesy [DBCB] example 16.9, pp. 803): consider relation schemas R(a, b, c) and S(c, d, e)
  
  
  
  
• 
• cannot be pushed past , , , , and
  • why? verify

---

References

[DBCB] H. Garcia-Molina, J. D. Ullman, and J. Widom. Database Systems: The Complete Book. Prentice Hall, 2002.