Introduction

Last week, we started looking at how query processing is done in DBMSs
We considered the stages of query processing: SQL -> Parsed Query -> Logical Query Plan -> Physical Plan -> Execute.
We said Logical Query Plans are usually expressed in terms of relational algebra operators in the plan, and we went over the most common operators. We also said these operators act on bags not sets.
We then said that physical plans pick particular algorithms to compute the relational algebra operators and might be more fined grained. Some example physical operators were Table-scan, Index Scan, Sort Scan.
There might be several (logical or physical) plans that would produce identical outputs to a fixed query, so our processor will need to choose amongst these.
To do so we need to introduce a cost model for a given physical plan. We defined several quantities that could be used in such a model: M, B(R), T(R), V(R, a).
We determined Cost(Table-scan(R)) = B(R) and Cost(Sort-scan(R)) = 3 * B(R) (if take at most two passes).
We then gave the multi-way merge sort algorithm and ran out of time.
Today, we review this algorithm again and consider one and two pass algorithms for different plan operators.

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 sorted lists at time?

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.
The one pass algorithm for union is one pass because it only reads the records in the relation once.
It is 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.

Quiz

Which of the following is true?

Quad-trees out of the box support multi-dimensional indexing on four attributes.
The `T(R)` cost parameter represents the number of blocks needed to store relation R.
Grid files support looking up nearest neighbors better than partitioned hash files.

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 neither `R` or `S` fits 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)`.

One and Two Pass Query Algorithms

Outline