Outline

• Finish One-Pass Algorithms
• In-Class Exercise
• Nested Loop Joins
• Sort Based Algorithms
• Two Pass Algorithms Based on Hashing

Introduction

• We have been looking at how queries are executed within a DBMS.
• We said queries are first converted into an internal representation called a logical query plan tree based on the relational algebra.
• We then considered how the operations in this tree can be converted to a physical query plan.
• The DBMS query optimizer often generates multiple such plans for the same query, then chooses among these the one with the lowest cost in the I/O model of computation.
• On Monday, we considered implementations and costs for several of the relational algebra operations.
• We assumed an iterator model for going through relations/outputs of the query so far, and tried to come up with ways to compute relational operations under this assumption.
• We said if we need to scan the base relations of a query only once, that the algorithm was one pass.
• If our algorithm, operates on a single tuple then either emits or doesn't a single result, the algorithm was tuple-at-a-time.
• Let's continue considering some more of these algorithms.

One Pass Algorithms for Binary Operations

Recall `M =` number of memory blocks; `B(T)` = number of blocks in table `T`.

• Set Union. Read S, do duplicate elimination and write to output. Then read `R` using same structure for duplicate elimination as used for `S`. Do duplicate elimination as write to output. Next `max(B(R), B(S)) le M` in this case.
• Set Intersection. Read `S` into `M-1` buffers, build search structure. Read each block of `R` and for each tuple `t` in `R` check if `t` is in this structure from S before writing to output. To do this need `min(B(R), B(S)) le M -1`.
• Set Difference. similar.
• Bag Intersection. Like set intersection, but we need to count the number of occurrences of `t` in `S` and `R` and output the minimum of these two numbers.
• Bag Difference. Similar. `R - S` if number of occurrences of `t` in `R gt` than the number of occurrences of `t` in `S` make difference in count many copies into `R - S`.

More One Pass Algorithms for Binary Operations

• Product and Natural Join.
  • If one of `R` and `S` fits into at most M-1 buffers, just read it entirely into main memory.
  • Then concatenate each of its tuples with the appropriate tuples from the other relation.
  • This takes time `B(R)` + `B(S)`.

In-Class Exercise

• Suppose we have two tables `R(A, B, C)` and `S(C, D, E)` as given below.
• `M=3` and each block can hold two records.
  

  R(A, B, C)
  a1	b1	c1
  a2	b2	c1
  a2	b3	c2

  S(C, D, E)
  c1	d1	e2
  c2	d1	e3

• Work out step-by-step how the algorithm of the previous slide would compute the natural join of these two tables.
• Post your solution to the Feb 28 In-Class Exercise Thread.

Nested Loop Joins

• What if one of `R` or `S` doesn't fit into memory?
• How do we do a join or product then?
• Could do:

  For each s in S do
      For each r in R do
          if r and s join make a tuple t then output t;
  

• Notice this algorithm fits fairly well within the iterator framework.
• However, if one is not being careful this could take time `T(R)cdot T(S)`. (`T(R)` = number of tuples in `R`.)
• We'll now discuss some improvements.

More Nested Loop Join

• Rather than being tuple oriented want to do our accesses on R and S in a block-wise manner.
• Want to store as much as we can of one of two relations in main memory. Assume `B(S) le B(R)`. Then can do:

  For each M-1 blocks of S do begin
      Read these blocks into main-memory buffers;
      Organize their tuples into a structure whose search
          attributes are the common attributes of R and S;
      For each block b of R do begin
          For each tuple t of b do begin
              find the tuples of S in main memory that join with t;
              output the join of t with each of these tuples;
          end;
      end;
  end;
  

• This is called nested block join.
• It takes time `B(S) + (B(S) cdot B(R))/(M-1)`.

Two Pass Algorithms Based On Sorting

• We'll next consider some algorithms based on sorting.
• We'll assume sorting only takes two passes.
• If we had `1GB = 10^{9}` bytes of memory for our DBMS buffers, then our n-way merge sort algorithm of last day could sort roughly `1 EB = 10^{18}` bytes of memory in two passes -- more data than what all but a small number of companies ever deal with.
• Despite this, sometimes performing a single pass can take a while (days), as anyone who has ever tried to make a duplicate of a large hard drive can tell you.

Duplicate Elimination using Sorting

Given `R` we sort `R` and output distinct values.

• Phase I creating sorted sublist of size `M` takes time `B(R)`.
• Phase II can be done in one pass provided can merge all of these sublists together in one go. So `B(R)` must be less than `M \cdot (M - 1)`.
• In which case, total cost is `3 cdot B(R)`.

Grouping and Aggregation using Sorting

• To do grouping and aggregation:
  1. Read the records into memory `M` blocks at a time.
  2. Sort `M` blocks using the grouping attributes as the sort key
  3. Write each sorted list to disk.
  4. Use one main buffer for each sublist, and initially load the first block of each sublist into its buffer.
  5. Repeatedly find the least value, `v`, of the (grouping attributes among the first available tuple in the buffer.
  6. For this value, we (a) compute all aggregates for the group `v` belong to. (b) if a buffer becomes empty replace it with the next buffer from the same sublist.
  7. If no more tuples for this v go on to next smallest `v`.
  8. Notice take `3B(R)` time and works if `B(R) lt M^2`

Sorting and Unions, Intersections, etc.

• Do Phase I of sort for both `R` and `S`. (Sort into subfiles of `M` blocks).
• Assume total number of subfiles generated `lt M-1`.
• In Phase II of sort compute the desired Union, intersection at the same time we are doing the merge phase.
• This takes `3(B(R)+B(S))` I/Os and the phase II step works provided `B(R)+B(S) le M(M-1)`.

Sort-based Join

• Want to join `R(X,Y)` and `S(Y,Z)`.
• Create sorted sublists of size `M` using join attribute `Y` for both `R` and `S`.
• Bring the first block of each sublist into a buffer (assume `le M` sublists).
• Repeatedly find the least `Y` value `y` among the sublist blocks in memory.
• Identify tuples in both relations having this `Y` value. Output the join of all these tuples.
• This takes time `3(B(R)+B(S))` provided `B(R)+B(S) lt M^2`.

Partitioning Relations by Hashing

• For the algorithms we'll consider next, it is useful to be able to read a file quickly into a hash table.
• To do this we use one block of main memory to read from the file and use M-1 blocks for each bucket of the hash table (we assume this has at most `M-1` buckets).
• We read successive blocks from the file apply the hash function to each tuple in these blocks and move these tuples to the correct bucket buffer.
• When a bucket buffer gets filled we write it to disk.

Hashed based Duplicate Elimination

• First hash file into bucket files.
• Then eliminate duplicates from each bucket file using the one pass algorithm and output the results.
• The idea is if `t` occurs multiple times, it will always hash to the same bucket. So can eliminate duplicates bucketwise.
• This works provided the hash function is random-like and `B(R)` is less than `M(M-1)`.
• The bound comes from the fact that each bucket will end up with roughly `(B(R))/(M-1)` blocks and we need to be able to read in whole buckets into memory to duplicate elimination, so we need `(B(R))/(M-1) < M`.
• It takes `3 B(R)` time.