---

Two-pass algorithm for ∩_Set (set intersection) based on hashing

---

---

---

 

Slideshow:

2-pass hash based algorithm for set intersection (R ∩_set S) - pass 1
 
Organization:   use 1 buffer to read R (or S)   and   (M−1) buffer to write R_i (or S_i)
Property of the tuples in a hash bucket
 
Same attribute value in R and S will be hashed into the same bucket:
 
2-pass hash based algorithm for set intersection (R ∩_set S) - pass 2
Pass 2: process each pair of sub-relation R_i and S_i using the one-pass   ∩_set algorithm:
 
Performance cost

Buffer requirement - key observation

Buffer requirement - estimate size
Assumed size of each sub-relation:

Minimum buffer requirement: (derive using the fact that min(B(R_i,B(S_i) ≤ M−1)

❮ ❯



---

---

---

---

• 2-pass R ∩_Set S based on hashing
  • Phase 1: hash both relations R and S using the same hash function:

    Notice that:

    Same attribute value in R and S will be hashed into the same bucket !!! Therefore: We can compute the intersection elements in the ∩set operation by examining the corresponding pair of buckets alone !!!

    ---

    ---

  • Phase 2: process each pair of sub-relation R_i and S_i using the one-pass   ∩_Set algorithm:

    See: One-pass Set Intersection algorithm

  ---

  ---

  ---

  ---

• Cost of the ∩_Set(R) operation
  • Cost incurred during Phase 1: (Hashing relations R and S)

    Read R: B(R) Write M-1 buckets (total): B(R) Read S: B(S) Write M-1 buckets (total): B(S)

    ---

  • Cost incurred during Phase 2: (run one-pass algorithm)

    Read M-1 buckets of R (total): B(R) Read M-1 buckets of S (total): B(S) Write R ∩Set S tuples: ≤ B(R) + B(S) (not counted - use buffer to pass !)

    ---

    ---

  • Total cost of the R ∩_Set S operation:

    Cost of ∩Set(R) = 3 B(R) + 3 B(S)

  ---

  ---

  ---

  ---

• Memory constraint
  • The one-pass algorithm used in Phase 2 has this memory constraint:

    All tuples of the smaller sub-relation Si must fit in the search structure using M−1 buffers: Therefore: min( B(Ri), B(Si) ) ≤ M−1 blocks The best case scenario of the hashing is: Every sub-relation has the same size: 1 B(Ri) = --- B(R) M-1 1 B(Si) = --- B(S) M-1 (Otherwise, some sub-relation Ri will larger than B(R)/(M−1)   or   some sub-relation Si will larger than B(S)/(M−1) !!!) Therefore: min( B(Ri), B(Si) ) ≤ M−1 blocks and 1 B(Ri) = --- B(R) M-1 1 B(Si) = --- B(S) M-1 We have: ==> (M-1)2 ≥ min( B(R), B(S) ) --------------------- <==> M-1 ≥ \/ min( B(R), B(S) ) Buffer requirement: -------------------- M ≥ \/ min( B(R), B(S) ) + 1