will give the same attributes and concatenated tuples regardless of order the

# Will give the same attributes and concatenated tuples

• Notes
• 106

This preview shows page 10 - 17 out of 106 pages.

will give the same attributes and concatenated tuples regardless of order (the attributes are named so the order of these does not matter) What about theta-join ? Commutative ( R 1 c S = S 1 c R ), but not always associative, e.g., R(a,b), S(b,c) , and T(c,d) (R 1 R.b<S.b S) 1 a<d T) = R 1 R.b<S.b (S 1 a <d T) 10 / 106
Algebraic Laws: Joins and Products Q : Does it matter in which order join or product are performed with respect to performance, e.g., R × S × T × . . . ? YES, it may be very important if only one of the relations fits in memory, we should perform the operation using this relation first - one-pass operation reducing the number of disk I/Os if joining or taking product of two of the relations in a large expression give a temporary relation which fits in memory, one should join these first to save both memory and disk I/Os one should try to make the temporary result as small as possible to save memory if we can estimate (using statistics) the amount of tuples being joined, we can save a lot of operations by joining the two relations giving fewest tuples first BUT, the final result will be the same 11 / 106
Note Carry attribute names in results, so order is not important Can also write as trees, e.g.: 1 1 R S T 1 1 S T R Different ordering in the execution of the 1 operations can produce different intermediate results (often with large difference in size of result sets) So one of the topics (problems) in query optimization will be: Find the optimal join ordering of a set of 1 operations 12 / 106
Algebraic Laws: Union and Intersect Union and intersection are both associative and commutative: Union ( ) is commutative: R S = S R Union ( ) is associative: R (S T) = (R S) T Intersection ( ) is commutative: R S = S R Intersection ( ) is associative: R (S T) = (R S) T 13 / 106
Laws for Bags and Sets Can Differ Example of an Algebraic Law that holds for set, but not for bags (e.g., distributive law of intersection over union) We know from Set Theory that A set (B set C) = (A set B) set (A set C) But, this law does not hold for bags: Suppose bags A , B , and C were each { x } A bag ( B bag C ) = { x } ∩ bag ( { x } ∪ bag { x } ) = { x } ∩ bag { x, x } = { x } ( A bag B ) bag ( A bag C ) = ( { x } ∩ bag { x } ) bag ( { x } ∩ bag { x } ) = { x } ∪ bag { x } = { x, x } 14 / 106
Algebraic Laws: Select Select is a very important operator in terms of query optimization reduces the number of tuples (size of relation) an important general rule in optimization is to push selects as far down the tree as possible Also can be helpful to break up a complex selection into parts Selection is idempoten . (multiple applications of the same selection have no additional effect beyond the first one) σ p (R) = σ p σ p (R) Select operations are commutative . (the order selections are applied in has no effect on the eventual result) σ p σ q (R) = σ q σ p (R) 15 / 106
Algebraic Laws: Select “Splitting” (AND and OR) laws: The selection condition involving conjunction of two or more predicates can be deconstructed into a sequence of individual select operations.

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