σ p1 p 2 R σ p1 σ p2 R σ p 2 σp 1 R σ p 1 p 2 R σ p 1 R set σ p 2 R 16 106

# Σ p1 p 2 r σ p1 σ p2 r σ p 2 σp 1 r σ p 1 p 2 r

• Notes
• 106

This preview shows page 16 - 25 out of 106 pages.

σ p 1 p 2 (R) = σ p 1 ( σ p 2 (R) ) = σ p 2 ( σ p 1 (R) ) σ p 1 p 2 (R) = ( σ p 1 ( R ) ) set ( σ p 2 ( R ) ) 16 / 106
Algebraic Laws: Select Push selections through the binary operators: product, union, intersection, difference, and join. if pushing select, select . . . 1. must be pushed through both arguments for union: Union: σ p (R S) = σ p (R) σ p (S) 2. must be pushed through first arguments, optionally second for difference: Difference: σ p (R - S) = σ p (R) - S σ p (R - S) = σ p (R) - σ p (S) 3. For the other operators it is only required that the selection be pushed to one argument. product, natural join, theta join, intersection e.g., σ p (R × S) = σ p (R) × S , if p contains only attributes from R 17 / 106
Algebraic Laws: Select If the condition p in σ p (R S) is compound ( p = p 1 and p 2 ), to split p up, we can use: σ p 1 p 2 (R) = σ p 1 ( σ p 2 (R) ) = σ p 2 ( σ p 1 (R) ) Example R(a,b) S(c,d) σ a =3 c =4 ( R S ) = σ a =3 ( σ c =4 ( R S ) ) = σ a =3 ( R σ c =4 ( S ) ) = σ a =3 ( R ) σ c =4 ( S ) 18 / 106
Algebraic Laws: Select If the selection condition p involves only the attributes of R and q involves the attributes of S , then the select operation distributes. σ p q (R 1 S) = σ p (R) 1 σ q (S) Let p = predicate with only R attributes q = predicate with only S attributes m = predicate with R,S attributes σ p (R 1 S) = σ p (R) 1 S σ q (R 1 S) = R 1 σ q (S) Some Rules can be Derived: σ p q (R 1 S) = σ p (R) 1 σ q (S) σ p q m (R 1 S) = σ m ( σ p (R) 1 σ q (S) ) Derivation for first one σ p q ( R 1 S ) = σ p ( σ q ( R 1 S ) ) = σ p ( R 1 σ q ( S ) ) = σ p ( R ) 1 σ q ( S ) 19 / 106
Algebraic Laws: Select: Pushing Selections Example Employee(fname, salary, dno) Dept(dname, dno) σ dname = Research ( Employee 1 Dept ) = Employee 1 σ dname = Research ( Dept ) Pushing down ” a selection ( σ ) will result in a smaller intermediate result set σ dname= Research 1 Employee Dept 1 Employee σ dname= Research Dept 20 / 106
Algebraic Laws: Select: Pushing Selections Example Employee(fname, salary, dno) Dept(dname, dno) σ dname = Research fname = John (Employee 1 Dept) = σ fname = John ( σ dname = Research (Employee 1 Dept) ) = σ fname = John ( Employee 1 σ dname = Research (Dept) ) = σ fname = John (Employee) 1 σ dname = Research (Dept) 21 / 106
Common used query optimization technique involving σ Simple query optimization The running time of database operations depends on: The size of the input relations (operands) Therefore: It is always beneficial (for running time) to reduce the size of the input relation(s) 22 / 106
Reducing the size of input relation using σ The selection operator σ can reduce the size of the input relation of some operators Example π movieTitle σ birthdate LIKE %1960 1 starName=name StarsIn MovieStar Input relations π movieTitle 1 starName=name σ birthdate LIKE %1960 StarsIn MovieStar Input relations The input relation of 1 in the second case σ birthday LIKE %1960 (StarsIn) can be much smaller than the input relation StarsIn 23 / 106
Simple query optimization technique: “push select down” One of the many query optimization techniques used by the DBMS is execute a σ p as soon as possible.

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