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. We said with even 1GB RAM buffer we could sort up to an exabyte `10^9` GB in two passes, and so these algorithms are likely to work for all practical DB applications.
We said many of the relational operators can be implemented using iterators (projection and selection) and briefly discussed iterators.
Today, we continue to consider one and two pass algorithms for different plan operators.

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.
On Wednesday, using iterators, we gave 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)`.

Quiz

Which of the following statements is true?

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

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.

Hash-Based Algorithm for Grouping and Aggregation

We want to do grouping and aggregation on `R`.
As with hash based duplicate elimination, the first step is to hash R into a hash file.
We want a hash function which only depends on the grouping attributes.
Now use the one pass algorithm for aggregation for each bucket.
So need that each bucket is `lt M`.
So works if `B(R) lt M(M-1)`.
It takes `3 cdot B(R)` time to do this operation (`2 cdot B(R)` to partition by hash, `B(R)` to go through each bucket).

Two Pass Group By Hash Example

Hash based and Unions, Intersections, etc

Hash both `R` and `S` to the same hash file.
Apply one pass algorithm to each of the bucket 1's of this.
Then apply the one pass algorithm to the bucket 2's,... etc.
This works provided `B(R) + B(S) lt M(M-1)`.
This takes time `3(B(R)+B(S))`.

Hash-Join

Use only the join attribute as the hash key.
Hash R and S to hash files of buckets.
Pick the hash files whose largest buckets are smaller.
As long as for this file the largest bucket is ` lt M`, we can read it into at most `M-1` buffer blocks, and use the other block to read from the other hash file
So in this situation can do one pass join of bucket 1's, 2's, etc.
Works provided `min(B(R), B(S)) lt M(M-1)`.
Takes time `3(B(R)+B(S))`.

Index-based Selection

Suppose have a clustering index and want to do `sigma_{a=v}(R)`.
Then the number of I/Os will be roughly `(B(R))/(V(R,a))`.
Here we are ignoring the cost of the index, roundoff errors, and that the blocks of `R` might not be completely filled with tuples from `R`.
If `R` is a non-clustering index, then the cost will be approximately `(T(R))/(V(R,a))`. So it will be more likely a table scan will be faster.

One and Two Pass Query Algorithms

Outline