Outline

• Introduction
• Quiz
• Physical Query Plan Operators
• One Pass Algorithms for Database Operations

Introduction

• At the end of last day, we said we were going to start talking about how query results are computed by the DBMS.
• We gave the flow chart above to illustrate the steps involved.

An Algebra for Queries

• One of the first steps in evaluating a query is to turn the original SQL into an internal representation, based on relational algebra, that we can then manipulate.
• We will view our algebra as operating on bags rather than sets.
• Bags (sometimes called multi-sets) are collections where order doesn't matter but where we do allow duplicates. For example, {{1, 2, 1,3}} = {{2, 3, 1, 1}} are the same multi-set. Neither of these is the same multi-set as {{1, 2, 3}}.
• We use bags since some of our operation like projecting onto attributes (columns) might result in outputs with duplicates.

Relational Algebra Expressions

• Relations `R_1, R_2 ...` are expressions.
• If `Q_1`, `Q_2` are an expression so are:
  1. `Q_1 \cup Q_2`, `Q_1 cap Q_2`, `Q1 - Q2`.
  2. `\sigma_{mbox{expr}}(Q_1)` where `mbox{expr}` is a valid selection expression.
     • Boolean formulas built out of things like `R.A=S.B` or `R.A=value`, `R.A ge value`, etc.
  3. `\pi_{A_1,...A_n}(Q_1)`. (Projection) Also, allow `A_j -> A_k` to mean renaming
  4. `Q_1 times Q_2` (Cartesian Product)
  5. $Q_1 \bowtie_{\mbox{expr}} Q_2$ (Natural Join is a special case)
  6. `delta(Q_1)` -- duplicate elimination on `Q_1`
  7. `gamma_L(Q_1)` -- Grouping and Aggregate Operators applied to `Q_1` (To handle COUNT, GROUP BY, HAVING)
  8. `tau_L(Q_1)` -- sort `Q_1` (ORDER BY)

Expression Trees

• Expression Trees are the intermediate representations of SQL queries.
• There can be many equivalent expression trees for the same query.
• Below is an example query, and a corresponding expression tree:

  SELECT title, birthdate 
  FROM MovieStar, StarsIn 
  WHERE year =1996 AND gender='F' AND starName = 'Starlet'
  

  

Quiz

Which of the following statements is true?

1. Sqlite3 databases are ISAM based.
2. A bitmap index is a kind of binary search tree.
3. A kd-tree satisfies the property that beneath a non-leaf node on attribute `A` with check `V`, the records to the left of the node are less than `V` and those to the right are greater than or equal to `V`.

Physical Query Plan Operators

• Physical Query Plans are similarly built out of operators each one of which would enable us to implement a relational operator operation (or at least part of one).
• Some example physical operators are:
  • Table-scan
  • Index Scan (can be more specific to index type)
  • Sort-Scan (tree-sort, main memory, merge)

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.

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.