CSE-4/562 Spring 2019

Query Evaluation Styles

All-At-Once (Collections): Bottom-up, one operator at a time.
Volcano-Style (Iterators): Operators "request" one tuple at a time from children.
Push-Style (Buffers): Operators continuously produce/consume tuples.

Table Scan ( $R$ )

Memory Required?: Constant!
IOs added?: $|R|$ tuples read

Preloading Data

Better Solution: Load both lhs and rhs records in blocks.


                    def apply_cross(lhs, rhs):
                      result = []

                      while r_block = lhs.take(100):
                        while s_block = rhs.take(100):
                          for r in r_block:
                            for s in s_block: 
                              result += [r + s]
                        rhs.reset()

                      return result

Cross product is expensive!
Can we do better?

$\sigma_c(R\times S) \equiv R\bowtie_c S$

Strategies for Implementing $R \bowtie_{R.A = S.A} S$

In-Memory Index Join (1-pass Hash; Hash Join): Build an in-memory index on one table, scan the other.
Partition Join (2-pass Hash; External Hash Join): Partition both sides so that tuples don't join across partitions.
Sort/Merge Join: Sort all of the data upfront, then scan over both sides.

Query Evaluation

Textbook: Ch. 15.1-15.5, 16.7

Query Evaluation Styles

Analyzing Volcano Operators

Memory Bounds

Disk IO

Accounting

Table Scan ( $R$ )

Select ( $\sigma(R)$ )

Select ( $\sigma(R)$ )

Project ( $\pi(R)$ )

Project ( $\pi(R)$ )

Union ( $R \cup S$ )

Union ( $R \cup S$ )

Cross ( $R \times S$ )

Cross ( $R \times S$ )

Cross ( $R \times S$ )

Cross ( $R \times S$ )

Nested-Loop Join

Preloading Data

Block-Nested Loop Join

Block-Nested Loop ( $R \times S$ )

Cross Product

Join Conditions

Strategies for Implementing $R \bowtie_{R.A = S.A} S$

Hash Functions

Hash Functions

1-Pass Hash Join

1-Pass Hash Join

2-Pass Hash Join

2-Pass Hash Join

Sort/Merge Join

Sort/Merge Join