---

Two-pass algorithm for ⋈ (join) based on hashing

---

---

---

 

Slideshow:

2-pass hash based algorithm for equi-join (R ⋈ 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 equi-join (R ⋈ S) - pass 2
Pass 2: process each pair of sub-relation R_i and S_i using the one-pass   ⋈ 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 ⋈ S based on hashing (= the Grace hash-join algorithm)
  • 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 process the (equality) joining tuples in the ⋈ 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   ⋈ algorithm:

    See: One-pass Join algorithm

  ---

  ---

  ---

  ---

• Cost of the R ⋈ S 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 on each pair of fragments)

    Read M-1 buckets of Ri (total): B(R) Read M-1 buckets of Si (total): B(S) Write R ⋈ S tuples: (discussed later) (not counted -- default: use memory buffer !)

    ---

    ---

  • Total cost of the R ⋈ S operation:

    Cost of ⋈(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 We assume that the hash function distribute the tuples in a uniform manner: Then every sub-relation (fragment/bucket) has approximately 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) ) Memory constraint: -------------------- M ≥ \/ min( B(R), B(S) ) + 1