Join Processing in Database Systems with Large Main Memories

Overview/Main Points

Background

Equijoin: R⋈ S WHERE R.a = S.b
Predicate join: R⋈ S WHERE p(R.a, S.b)
Suppose that R has |R| pages and m rows per page, and S has |S| pages and n rows per page.
|R| ≤ |S|
Buffer pool has M pages.

Nested Loops Join

for r in R
  for s in S
    if r.a = s.b
      output <r, s>.

At the worst case, it needs ( |R| + m * |R| * |S| ) I/Os.
- M < |S|
- use FIFO/LRU as page replacement algo

Page Nested Loop

for each page of R
  scan S, comparing each s tuple to all r tuples in the current page.
    if r.a = s.b
      output <r, s>.

At worst case, it needs ( |R| + |R| * |S| ) I/Os.
Occupied one more page than the simple NLJ.

Block Nested Loop

for each block of R
  scan S, comparing each s tuple to all r tuples in the current block.
    if r.a = s.b
      output <r, s>.

At worst case, it needs ( |R| + ^|R| ⁄ _b * |S| ) I/Os, where b represents the number of pages per block.
Occupied b more pages than the simple NLJ.

Sort merge join

    sort R on R.a.
    sort S on S.b.
    merge sorted R * S, join and output <r, s>.

Sort in one pass, and merge in another pass.
On average it needs O(|R| log |R| + |S| log |S| + |R| + |S|) time, and at the worst case 3(|R| + |S|) I/Os.
|S| < M: S should be fit into memory; otherwise, use external sort.

Index Nested Loops

    for each r in R
      lookup r.a in the index on S.b.
        find all matches, output <r, s>.

Suppose there is an index on S.b
At worst case, it needs ( |R| + m * |R| * f ) I/Os, where f is the number of page reads in an index lookup.
- clustered B⁺-tree index: 3 - 5 due to its large fan-out.
- hash index: 1.5 on average
No |S|, so great if R is small.

Block Index Nested Loops

sort the tuples in the ( M - 2 ) pages on the join key

Hash join (classic hashing)

    scan R, partitioning it into R₁, R₂, ..., R_n by hashing the joining attribute via h₁.
    scan S, partitioning it into S₁, S₂, ..., S_n by hashing via h₁.
    for i = 1 to n do
      R_i⋈ S_i.

    R_i⋈ S_i:
      pick n such that R_i's fit in memory, build a hash table on R.a via h₂.
      for i = 1 to n
        read R_i into memory and build the hash table.
        scan S_i sequentially, joining with R_i by probing the hash table with the matching hash value.

Idea: partition R and S into disjoint subsets and then join corresponding subsets.
Hash function: If h(r.a) = k, then r goes to partition k. Ex: h(x) = x mod (n+1).
Intuition: Do not do join R_i⋈ S_j where i ≠ j.
It needs 3 * ( |R| + |S| ) I/Os.
Fundanmentals of parallel db.

Simple hash join

    
    1) scan the smaller relation. If the current tuple hashes to current bucket, then install it in an in-memory hash table; otherwise write to disk.
    2) scan the other relation. If the current tuple hashes to the current bucket, probe the hash table for a match; otherwise write to disk.
    3) repeat until done.

h(x) = ƒ(x) mode #, where h is a universal hash function, ƒ is a complex function, and # is a big number.
h₁(x) = h(x) mod (n + 1), which determines which bucket of R (1, 2, ..., n).
h₂(x) = h(x) mod (m + 1), which is used for the in-memory hash table whose size is m on a single partition at a time.
How to pick up n?
- n = 1, spectacular as it needs (|R| + |S|) I/Os
- n = 2, good because of less than (|R| * 2 + |S| * 2) I/Os.
- n = 1000, bad due to too many I/Os.
- performance has an inverse ratio with n.

GRACE hash join

    
    partition R into R₁, R₂, ..., R_n on disk.
    partition S into S₁, S₂, ..., S_n on disk.
    for i = 1 to n do
      read R_i, build an in-memory hash table.
      read S_i, probe the above hash table.

GRACE uses a hash function for partitioning, which turns out that the subsets of R have approximately equal size under the assumption that a tuple of S joins with at most one block of tuples of R. But if R contains many tuples with the same joining attribute value, then this partitioning may not be possible.
Two constrains result in sqrt(|R|) < M
- |R_i| < M, which means that each partition for R fits in memory whose page size is M.
- n < M, which means that at least 1 output page per partition
- Thus, n * |R_i| < M² → |R| < M² → sqrt(|R|) < M
With little memory available, works better than the simple hash by avoiding repeatedly scan of both R and S.
When most of R fits into memory, performs much poorer than the simple hash due to (|R| * 2 + |S| * 2) I/Os.
|S_i| ≮ M so that in the second phase using hash outperforms sort-merge.

Hybrid hash join

    
    scan R, leaving R₀ in memory, writing R₁, ..., R_n to separate files.
    scan S, probing S₀ into R₀, leaving S₁, ..., S_n on disk.
    for i = 2 to n do
      read R_i into memory, build an in-memory hash table.
      scan S_i, probing the above hash table.

It needs 3 * ( |R| + |S| ) - 2 * ( |R₀| + |S₀| ) I/Os.
Leaving R₁ & R₂ in memory VS choosing smaller n
More memory consume for keeping the hash table for |R₀| in memory.

Cost Model

{R}, {S}: # tuples in R and S
|R|, |S|: # of pages in R and S
hash: time to hash a tuple
move: time to copy a tuple to a buffer
comp: cost of comparing tuples
I/O: cost of reading or writing a page
F: > 1 (usually 1.1); a universal fudge factor
q: fraction
Total cost:

    ({R} + {S}) * hash +            // partition cost
    ({R} + {S}) * (1 - q) * move +  // move to output buffers
    ({R} + {S}) * (1 - q) * I/O  +  // write to disk
    ({R} + {S}) * (1 - q) * I/O  +  // read into memory
    ({R} + {S}) * (1 - q) * hash +  // build & probe 2-n passes
    {R} * move                   +  // move tuples to hash tuples for R
    {S} * comp * F                  // probe each tuple in S; more than one comparison

Partition Overflow

The subsets of the partitions of R and S is required to fit in memory.
Choosing a hash function h
- Assume a uniform distribution and choose a partition of h's hash values accordingly.
- Store statistics about the distribution of h-values.
- Using sampling techniques with the identity function, rather than a hash function, to collect distribution statistics in a reasonable time
Overflow on disk for GRACE and hybrid hash
1. Scan and re-partition into two pieces for R's overflowed partitions. Alternatively, cut it into one piece that fits in memory, and add the rest into R's another smaller partition.
2. A similar adjustment should be made for the S counterpart so that the partitions of R and S will correspond pairwise to the same hash values.
Overflow in memory for simple hash
1. Reassign some buckets on disk.
2. Continue processing with a slightly modified partitioning hash function.
3. Use the modified hash function for processing S.
Overflow in memory for hybrid hash
1. For R₀'s overflowed hash table, the similar stratey for simple hash could be applied: create a new buffer for some buckets, or spread over others if smaller partitions exist.

When do join algorithms produce the first result triple?

Nested Loops & Index Nested Loops: right away
Sort-merge, hashing: takes a while (after sorting)

When do join algorithms produce the last result triple?

Nested Loops & Index Nested Loops: after iterating the inner relation for the last unit in the outer relation
Sort-merge, hashing: after probing the larger relation in the hash table built with the smaller relation.

Averaging: join below tuples

Symmetric Hash Join

    start scanning R & S concurrently
      when a tuple r ∈ R arrives,
        probe the hash table on S, looking for answers.
        insert in R hash table.
      when a tuple s ∈ S arrives,
        probe the hash table in R, looking for answers.
        insert in S hash table.

Suppose two disks or distributed storages for R/S.
Need to fit in memory, or flush to disks and then bring back to memory.
Correctness: Consider an answer tuple ‹ r, s ›. If the r tuple is processed first, then it will not find s, and will be inserted into the R hash table. When s arrives, it will find r in the r hash table, so ‹ r, s › will be generated. The reverese (s arriving before r) is symmetric. So each answer tuple is generated once and exactly once.

Tools for increasing the efficiency of join processing

database filter mechanism introduced in Logic per Track Devices: send records only qualified from the disk to the database.
Babb array (powerful if a high selectivity, i.e., few matching tuples for the join)
1. Build a boolean array whose bit corresponds to a hash bucket and is set if an R tuple hashes to that bucket during partitioning R.
2. When s arrives, check the boolean array, instead of probing R's hash table, for whether there is a match in the same bucket.
semijoin (reduce significantly # of S to be processed if a low selectivity)
- Algorithm
  1. Construct the projection of R on its joining attributes. (π (R))
  2. Join π (R) to S. (S⋉ R, called the semijoin of S with R, the set of S tuples that participate in the join of R and S.)
  3. Join S⋉ R to R, equivalent to the join of R and S.
- Need large space to store the projection; mitigate by a Babb array.

Join Processing in Database Systems with Large Main Memories

One-line Summary

Overview/Main Points