σ 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

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
Image of page 16
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
Image of page 17
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
Image of page 18
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
Image of page 19
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
Image of page 20
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
Image of page 21
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
Image of page 22
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
Image of page 23
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.
Image of page 24
Image of page 25

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