Finish Two Pass Query Algorithms - Query Parsing and Optimization

CS157b

Chris Pollett

Mar 4, 2020

Outline

Two Pass Algorithms Based on Hashing
Indexed Based Algorithms
In-Class Exercise
Parsing
Parse Trees and Grammars
Algebraic Laws for Improving Query Plans
From Parse Trees To Logical Query Plans

Introduction

On Monday, 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.
Today, we start by considering hash-based two pass algorithms.

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.

In-Class Exercise

Suppose a block can hold three records where a record just consists of a single integer value, we have three blocks of main memory, and we want to eliminate duplicates from a file containing records 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3.
Use mod 2 as your hash function and show step-by-step how two pass duplicate elimination would work in this situation. (Please try to type answer rather than upload image).
Please post your solution to the Mar 4 In-Class Exercise Thread.

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.

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 = S cap R`; `(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 replace 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.