Outline

• Two Pass Algorithms Based on Hashing
• Indexed Based Algorithms
• Quiz
• Parsing
• Parse Trees and Grammars
• Algebraic Laws for Improving Query Plans
• From Parse Trees To Logical Query Plans

Introduction

• On Wednesday, we were considering two pass algorithms for various database operations such as duplicate elimination, group and aggregation computations, set operations, and join.
• The first set of two-pass algorithms were based on sorting. 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 then started presenting hash-based two pass algorithms.
• We said these algorithms first involved hashing a file into a set of buckets.
• If we have `M` blocks of RAM, we created `M-1` buckets.
• We read in a block from file to hash. Hash each record in the block to the buckets, read in the next block.
• If the RAM block used for a bucket got full we wrote it to disk and emptied it.
• If original file had size less than `M(M-1)` said each bucket would take about `M` blocks.
• So an operation like duplicate elimination could be done by reading each bucket into memory, sorting and eliminate duplicates.
• Let's look at other hash-based two pass algorithms...

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.

Index-based Join

• Examine each block of R. For each tuple t in that block, look up all things that join with it in S and output the joins.
• Cost will approximately be `(T(R) cdot T(S))/(V(S,Y))`, where `Y` is the join attribute if `S` is nonclustered and `(T(R) cdot B(S))/(V(S,Y))` if it is clustered.

Quiz

Which of the following statements is true?

1. The I/O model cost for a table scan was Cost(Table-Scan(R)) = T(R)
2. Duplicate elimination can always be done in one pass.
3. One reason what the time for sort-based duplicate elimination was `3 \cdot B(R)` was we assumed we didn't need to write the results back to disk.

Parsing Queries

• We now begin to discuss how queries are parsed. Below is a rough flow of the operations:
  

Syntax Analysis and Parse Trees

• The job of the parser is to take SQL and convert it to a parse tree.
• Nodes in this tree can be of the following types:
  • Atoms -- keywords (like SELECT), names of attributes, constants, parentheses, operators. These are leaves in the tree
  • Syntactic Categories -- names for families of query subpart. For example, <Condition> might represent a possible condition part in the query.

A Grammar for a Simple Subset of SQL

• Here are some example rules a parser might use for SQL:

  <Query> ::= <SFW>
  <Query> ::= (<Query>) // '::=' should be read as 'can be expressed as' 
  <SWF> ::= SELECT <SelList> FROM <FromList> WHERE <Condition> 
  <SelList> ::= <Attribute>, <SelList>
  <SelList> ::= <Attribute>
  <FromList> ::= <Relation>, <FromList>
  <FromList> ::= <Relation>
  <Condition> ::= <Condition> AND <Condition>
  <Condition> ::= <Tuple> IN <Query>
  <Condition> ::= <Attribute> = <Attribute>
  <Condition> ::= <Attribute> LIKE <Pattern> //etc....
  

Example

SELECT name
FROM MovieStar
WHERE birthdate LIKE '%1960'

Might yield parse tree:

The Preprocessor

• The Preprocessor does semantic checking. That is:
  • It expands views to their corresponding query
  • It checks that the items in the FROM clause are actual tables in the DB
  • It checks that the attributes in the SELECT clause belong to some table and matches them to the correct table.
  • It checks that attributes in the WHERE clause are being put in conditions that are appropriate for their type.

Algebraic Laws For Improving Query Plans

• We are now going to consider different ways we can write the same query.
• The hope being some ways of writing are faster to process than others.
• For instance, it is probably faster to compute selects before taking a join, then vice versa.
• The optimizer's job will be to consider several plans and usually heuristically choose the best one.

Commutative and Associative Laws

• `R times S = S times R`; `(R times S) times T = R times (S times T)`.
• $R \bowtie S =S \bowtie R$; $(R \bowtie S) \bowtie T = R \bowtie (S \bowtie T)$
• `R cup S = S cup R`; `(R cup S) cup T = R cup (S cup T)`
• `R cap S = R cap S`; `(R cap S) cap T = R cap (S cap T)`
• Notice the intermediate tables in computing a sequence of joins can be considerable smaller depending on the order of execution.

Laws Involving Selection

• `sigma_{mbox{C1 AND C2}}(R) = sigma_{C1} (sigma_{C2}(R)) = sigma_{C2} (sigma_{C1}(R))`
• `sigma_{mbox{C1 OR C2}}(R) = sigma_{C1}(R) cup sigma_{C2}(R)` provided `R` is a set.
• `sigma_C(R cup S) = sigma_C(R) cup sigma_C(S)`
• `sigma_C(R-S) = sigma_C(R) - S`
• Similar rules hold for joins and Cartesian products.

Pushing Selections

• To reduce the cost of computing joins and products as much as possible, a good heuristic is to try to push selects as far down all branches in the query tree as possible
• For instance,
  $\sigma_{\mbox{year=1996}}(Movie \bowtie StarsIn)$
  should be rewritten
  $(\sigma_{\mbox{year=1996}}(Movie)) \bowtie (\sigma_{\mbox{year=1996}}(StarsIn))$.

Laws Involving Projection

• $\pi_L(R \bowtie S)= \pi_L(\pi_M(R)\bowtie \pi_N(S))$ where `M` is those attributes in `L` that are from R; `N` is those attributes in `L` that are from `S`.
• This also works for joins with conditions on them.
• As with selections, it is often useful to try to push projections as far down the tree as possible.

Laws for Joins and Products

• We have already seen that it is possible to rewrite a join using:
  $R \bowtie_{Condition} S = \sigma_{Condition}(R \times S)$
• A natural join can be written as:
  $R \bowtie S = \pi_L(\sigma _C (R \times S))$ where `L` is the attributes from the natural join and `C` is the condition which matches identically named attributes of `R` and `S`.

From Parse Trees to Logical Query Plans

• After calculating the parse tree for a query...
• We convert the appropriate groups in the tree by relational operators.
  • For example, we replace the node in a tree with the cartesian product of the tables listed under it.
• Then try to optimize this relational algebra tree to create a physical query plan.
  • Might apply heuristics like pushing selection and projections which we mentioned earlier.