Outline

• Implementing Physical Query Operators
• Quiz
• 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.
• We begin today by looking at an I/O model for some of the physical plan operators used to evaluate a query.
• We then look at some algorithms that can be used to implement the physical query plan based on the I/O model.
• We split these algorithms into two types: one an two-pass algorithms...

Model of Computation for Physical Operators

• A basic model to compute the cost of a query is to just count the number of disk I/Os.
• To this model, we might later add network communication costs.
• We assume the arguments for any query come from disk, but results are left in main memory.
• This is reasonable as the query processor will likely feed the results of one operation in the plan into the next.
• I.e., The query processor will pipeline operations.

Parameters for Measuring Cost

• M - number of main memory blocks available
• B(R) - number of blocks in R. (Just B if R understood.)
• T(R) - number of tuples in R. (Just T if R understood.)
• V(R,a) - number of distinct values of attribute a.

Quiz

Which of the following statements is true?

1. Grid files can be used for neighborhood lookup queries such as find all points in a rectangle.
2. Any k-d tree is a multi-key index (as the latter was described in class).
3. Quad-trees are typically used to provide an index for 3D data.

I/O Cost for Scan Operators

• Cost(Table-Scan(R)) = B(R)
• Cost(Sort-Scan(R)) =
  • B -- provided R fits in main memory.
  • 3B -- if use multiway merge sort (we'll describe next slides) and only need one pass in Phase II.

Aside - Sorting in Secondary Storage

• Example: Assume have a table R consisting of 50,000,000 rows. Assume block size is 16KB (`2^{14}` bytes) and 100 records fit in a block. i.e., 1 record is roughly 160 bytes.
• So `R` fits in 500,000 blocks.
• On the other hand, if have 500MB of memory. Then can at most hold `500 \cdot 2^{20}/2^{14} = 32,000` blocks.
• So if we want to sort on some field, we can't read everything into RAM.

Bottom up Merge Sort

• Common main memory sort algorithm
• Work in passes. In pass `i` merge adjacent runs of length `2^i` into runs of length `2^{i+1}`.

  Ex 
  4 2 5 1 6 3 (runs length 1)
  2 4 1 5 3 6 (runs length 2)
  1 2 4 5 3 6 (runs length 4)
  1 2 3 4 5 6 (runs of length 8) -- done
  

• So have about `log_2 n` passes and each pass is `O(n)` work. So `O(n\cdot\log_2 n)` time algorithm.

Two Phase Multiway Merge-Sort (TPMMS)

• Phase I -- read in M blocks into main memory (number of blocks of main memory have for buffers). Sort these according to the desired field. Save these sorted sub- lists back to disk.
• Phase II -- merge these sub-lists together to produce one sorted list. We will explain how this is done in a moment.

Phase I Example

• Using the R from a couple slides ago...
  • R was 500,000 blocks long and main memory holds 32,000 blocks.
  • So would make 500000/32000 = 16 sub-lists.
  • Each block from R needs to be read once from disk and written once to disk for a total of 1,000,000 I/Os.
  • If an I/O takes 1/100 of second. This would take 10,000 seconds of 10000/3600 = 2.7 hours

Phase II

• To do this phase we could do something like in the main memory case -- work in passes, read a block each from pairs of adjacent sorted sub-lists, merge these, and write to a twice as long output sub-list. Do this for all sub-lists. After `log_2 n` passes we would be done.
• This is somewhat inefficient, since only have two blocks from the file in memory at a time.
• Instead, we read from `M-1` different sub-lists one block. We merge these into one output block. When it fills write it disk and start filling it again.

Phase II -cont'd (finish sorting in secondary storage aside)

How much more efficient is this last idea than just merging two blocks?

• Well, now it takes `log_{M - 1} n` passes rather than `log_2 n` passes.
• As each pass can take a while this can mean significant savings.

Iterators for Implementing Physical Operators

• Many physical operators are implemented using Iterators. An iterator is an object which supports:
  • Open
  • GetNext
  • Close
• Example: A one pass algorithm for `R cup S` might
  1. First open `R` use GetNext in a loop to get each tuple in `R` till `R` done.
  2. Then Open `S` and use GetNext in a loop to get each tuple of `S`.
  3. Finally, Close both `R` and `S`.

One Pass Algorithms -- Tuple at a Time Operations

• The basic format of these algorithms is:
  1. Read blocks of R one at a time into an input buffer
  2. Perform an operation on each tuple
  3. Move result to the output buffer or perform next step in query process.
• We just saw a one pass algorithm for union.
• It is one pass because it only reads the records in the relation once.
• It was also an example of a tuple-at-a-time algorithm, as when it was done processing the tuple it could be used by the next stage of the query processor.
• Some other examples of one pass, tuple-at-a-time operations are `pi(R)`and `sigma(R)`.
• Cost will typically be `B(R)` if `R` is on disk.

One-Pass Algorithms for Unary, Full-Relation Operations

• Duplicate Elimination
  • Read each block into memory.
  • Use `M-1` blocks to store tuples seen so far.
  • Before putting a tuple in the output block check if it is among those seen.
  • To work this algorithm requires `B(delta(R)) le M-1`.
  • To simplify future discussion, we will often approximate as `B(delta(R)) le M`.
• Grouping
  • For MIN(a) and MAX(a), we can scan and keep copy of current min or max of group so far.
  • For COUNT, add one for satisfying value.
  • SUM and AVG are similarly handled. Need to have a bound like above on number of distinct groups.

One Pass Algorithms for Binary Operations

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

• Set Union. Read `S` keeping one unique copy of every tuple seen in memory. Then read `R` using same structure for duplicate elimination as used for `S`, , write unique tuples to output. For this one pass algorithm to work we need `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)`.

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`.