---

Laws involving selection (σ)

---

---

---

 

Slideshow:

Laws involving σ
 
Laws involving σ:
σC1 and C2(R) σC1 or C2(R) σCond(R1 ∪ R2)) σCond(R1 ∩ R2)) σCond(R1 − R2)) σCond(R1 × R2)) σCond(R1 ⋈ R2))
Comment: the conditions only contain attributes from one relation
σ _{C1 and C2} (R)
 
Comment on Algebraic Laws: direction
 
Comment:

σ _{C1 or C2} (R) - valid for set union
 
Bag union (RHS) result may contain duplicates
σ _Cond (R ∪ S)
 
Comment: performing the selection σ earlier may reduce the input size or allow use to use index scan
σ _Cond (R − S)
 
Note: the sets R and S but have same structure to perform subtraction (−)
Comment: performing the selection σ earlier may reduce the input size or allow use to use index scan
σ _Cond (R ∩ S)
 
Comment: performing the selection σ earlier may reduce the input size or allow use to use index scan
Combining   (1) σ _{C1 and C2} (R)   and   (2) σ _Cond (R ∩ S)
 
σ _Cond (R × S) - with Cond containing only attributes from R or S
 
Comment: performing the selection σ earlier may reduce the input size or allow use to use index scan
Note: a more useful law on σ and × will be discussed in next webpage
σ _Cond (R ⋈ S) - with Cond containing only attributes from R or S
 
Comment: performing the selection σ earlier may reduce the input size or allow use to use index scan
 
σ _Cond (R ⋈ S) - with Cond containing only attributes from R or S - Example
 
Clearly: performing the selection σ earlier may reduce the input size or allow use to use index scan
Combining   (1) σ _{C1 and C2} (R)   and   (2) σ _Cond (R ⋈ S)
 
❮ ❯



---

---

---

---

• σ_{C1 and C2}(R)                  (very useful !!!)
  • The following are valid for sets and bags:

    σC1 and C2(R) = σC1( σC2( R ) ) = σC2( σC1( R ) )

    Example:

    σsalary>50000 and dname='R'( employee ⋈ department ) = σsalary>50000 ( σdname='R'( employee ⋈ department ) )

    Comment:

    Later in this webpage, you will learn another law used to push a selection (σ) further down and obtain a more optimal expression

  ---

  ---

  ---

  ---

• A comment before we go further....
  • Fact:

    Every Algebraic law can be used in either direction: Sometimes, we may want to replace: σC1 and C2(R) ⇒ σC1( σC2( R ) ) And other times, we can replace: σC1( σC2( R ) ) ⇒ σC1 and C2(R)

  ---

  ---

  ---

  ---

• σ_{C1 or C2}(R)
  • The following is only valid for sets:

    If R is a set, then this set union holds: (If x ∈ R satisfies both C1 and C2, a bag union will produce 2 tuples !!!)\

    Example:

    σlname='S' or dname='R'( employee ⋈ department ) = σlname='S'( employee ⋈ department ) ∪ σdname='R'( employee ⋈ department )

    Comment:

    We can push the selection further down the tree (see later discussion in this webpage There is an opportunity to do better if: There is an index on lname in employee and an index on dnumber in department There is an index on dname in department and an index on ssn in employee

  ---

  ---

  ---

  ---

• σ_C(R ∪ S)
  • The following is valid for sets and for bags :

    In general:

  ---

  ---

  ---

  ---

• σ_C(R − S)
  • The following is valid for sets and for bags :

    In general:

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• σ_C(R ∩ S)
  • The following is valid for sets and for bags :

    In general:

    ---

    ---

  • Note:

    If the condition C in σ C (R ∩ S) is compound (C = C1 and C2), we can use: σC1 and C2(R) = σC1( σC2( R ) ) = σC2( σC1( R ) ) to split C up. Example:

  ---

  ---

  ---

  ---

• σ_C(R × S)
  • The following is valid for sets and for bags :

    In general:

    (A more useful law about σ and × will be discussed soon)

  ---

  ---

  ---

  ---

• σ_C(R ⋈ S)         very useful law !!!!
  • The following is valid for sets and for bags :

    In general:

    Example:

    Employee (fname, salary, dno) Department (dname, dno)

    Comment:

    "Pushing down" a selection (σ) will result in a smaller intermediate result set Optimization opportunities: The one-pass algorithm has lower memory requirement using a smaller intermediate relation as input The index-join algorithm can achieve a lower cost than a one-pass algorithm (see: click here)

    ---

    ---

    ---

    ---

  • Note:

    If the condition C in σ C (R) is a conjunction(C = C1 and C2), we can use: σC1 and C2(R) = σC1( σC2( R ) ) = σC2( σC1( R ) ) to split C up.

    Example: