Outline

• Expression Trees
• Implementing Physical Query Operators
• In-Class Exercise
• Multi-wat Sorting
• Iterators for Query Operations

Introduction

• On Monday, we finished talking about multi-dimensional indexes.
• We then began looking at how queries are executed within a DBMS.
• We gave an overall diagram of the stages of query execution in DBMSs.
• We said the first stage is to create from the query an internal representation, called a logical query plan tree, based on the relational algebra.
• We said the outputs of such operators will generally be bags not sets. I.e, multi-sets, where order of elements does not matter but number of repeats does.
• We start today by looking at the relational operators which are used in such a plan.

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 ⋈_{\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). For these, it is important that we are using bags!!
  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'
  

  

In-Class Exercise

• Suppose we have an PERSON(PERSON_ID, FNAME, LNAME, BIRTHDATE, MOTHER_ID, FATHER_ID) table.
• Give a SQL query to output alphabetically the whole name and birthdate of all great-grandmothers of someone whose first name is Beatrice.
• Give an expression tree for that query.
• Please post your solution to the Mar 1 In-Class Exercise Thread.

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