Evaluation of Relational Operations

1
Evaluation of Relational Operations

• Chapter 14, Part A (Joins)

2
Outline

• Relational Operations review
• Joins -equality join
• Simple Nested Loop Join implementation
• Index Nested Loop Join
• Block Nested Loop Join
• Sort-Merge Join
• Hash-Join
• Summary

3
Relational Operations

• We will consider how to implement
• Selection ( ) Selects a subset of rows
  from relation.
• Projection ( ) Deletes unwanted columns
  from relation.
• Join ( ) Allows us to combine two
  relations.
• Set-difference ( ) Tuples in reln. 1, but
  not in reln. 2.
• Union ( ) Tuples in reln. 1 and in reln. 2.
• Aggregation (SUM, MIN, etc.) and GROUP BY
• Since each op returns a relation, ops can be
  composed! After we cover the operations, we will
  discuss how to optimize queries formed by
  composing them.

4
Schema for Examples
Sailors (sid integer, sname string, rating
integer, age real) Reserves (sid integer, bid
integer, day dates, rname string)

• Reserves
• Each tuple is 40 bytes long, 100 tuples per
  page, 1000 pages.
• Sailors
• Each tuple is 50 bytes long, 80 tuples per page,
  500 pages.

5
Equality Joins With One Join Column
SELECT FROM Reserves R1, Sailors S1 WHERE
R1.sidS1.sid

• In algebra R S. Common! Must be
  carefully optimized. R S is large so, R
  S followed by a selection is inefficient.
• Assume M tuples in R, pR tuples per page, N
  tuples in S, pS tuples per page.
• In our examples, R is Reserves and S is Sailors.
• Cost metric of I/Os. We will ignore output
  costs.

6
Method 1Simple Nested Loops Join
foreach tuple r in R do foreach tuple s in S
do if ri sj then add ltr, sgt to result

• For each tuple in the outer relation R, we scan
  the entire inner relation S.
• Cost M pR M N 1000 1001000500
  I/Os.
• Page-oriented Nested Loops join For each page
  of R, get each page of S, and write out matching
  pairs of tuples ltr, sgt, where r is in R-page
  and S is in S-page.
• Cost M MN 1000 1000500
• If smaller relation (S) is outer, cost 500
  5001000

7
Method 2 Index Nested Loops Join
foreach tuple r in R do foreach tuple s in S
where ri sj do add ltr, sgt to result

• If there is an index on the join column of one
  relation (say S), can make it the inner and
  exploit the index.
• Cost M ( (MpR) cost of finding matching S
  tuples)
• For each R tuple, cost of probing S index is
  about 1.2 for hash index, 2-4 for B tree. Cost
  of then finding S tuples (assuming Alt. (2) or
  (3) for data entries) depends on clustering.
• Clustered index 1 I/O (typical), unclustered
  upto 1 I/O per matching S tuple.

8
Examples of Index Nested Loops

• Hash-index (Alt. 2) on sid of Sailors (as inner)
• Scan Reserves 1000 page I/Os, 1001000 tuples.
• For each Reserves tuple 1.2 I/Os to get data
  entry in index, plus 1 I/O to get (the exactly
  one) matching Sailors tuple. Total 220,000
  I/Os.
• Hash-index (Alt. 2) on sid of Reserves (as
  inner)
• Scan Sailors 500 page I/Os, 80500 tuples.
• For each Sailors tuple 1.2 I/Os to find index
  page with data entries, plus cost of retrieving
  matching Reserves tuples. Assuming uniform
  distribution, 2.5 reservations per sailor
  (100,000 / 40,000). Cost of retrieving them is
  1 or 2.5 I/Os depending on whether the index is
  clustered.

9
Method 3 Block Nested Loops Join

• Use one page as an input buffer for scanning the
  inner S, one page as the output buffer, and use
  all remaining pages to hold block of outer R.
• For each matching tuple r in R-block, s in
  S-page, add ltr, sgt to result. Then read
  next R-block, scan S, etc.

R S
Join Result
Hash table for block of R (k lt B-1 pages)
. . .
. . .
Input buffer for S
Output buffer
10
Examples of Block Nested Loops

• Cost Scan of outer outer blocks scan of
  inner
• outer blocks
• With Reserves (R) as outer, and 100 pages of R
• Cost of scanning R is 1000 I/Os a total of 10
  blocks.
• Per block of R, we scan Sailors (S) 10500
  I/Os.
• If space for just 90 pages of R, we would scan S
  12 times.
• With 100-page block of Sailors as outer
• Cost of scanning S is 500 I/Os a total of 5
  blocks.
• Per block of S, we scan Reserves 51000 I/Os.
• With sequential reads considered, analysis
  changes may be best to divide buffers evenly
  between R and S.

11
Method 4 Sort-Merge Join (R S)

• Sort R and S on the join column, then scan them
  to do a merge (on join col.), and output
  result tuples.
• Advance scan of R until current R-tuple gt
  current S tuple, then advance scan of S until
  current S-tuple gt current R tuple do this until
  current R tuple current S tuple.
• At this point, all R tuples with same value in Ri
  (current R group) and all S tuples with same
  value in Sj (current S group) match output ltr,
  sgt for all pairs of such tuples.
• Then resume scanning R and S.
• R is scanned once each S group is scanned once
  per matching R tuple. (Multiple scans of an S
  group are likely to find needed pages in buffer.)

12
Example of Sort-Merge Join

• Cost M log M N log N (MN)
• The cost of scanning, MN, could be MN (very
  unlikely!)
• With 35, 100 or 300 buffer pages, both Reserves
  and Sailors can be sorted in 2 passes total join
  cost 7500.

(BNL cost 2500 to 15000 I/Os)
13
Refinement of Sort-Merge Join

• We can combine the merging phases in the sorting
  of R and S with the merging required for the
  join.
• With B gt , where L is the size of the
  larger relation, using the sorting refinement
  that produces runs of length 2B in Pass 0, runs
  of each relation is lt B/2.
• Allocate 1 page per run of each relation, and
  merge while checking the join condition.
• Cost readwrite each relation in Pass 0 read
  each relation in (only) merging pass ( writing
  of result tuples).
• In example, cost goes down from 7500 to 4500
  I/Os.
• In practice, cost of sort-merge join, like the
  cost of external sorting, is linear.

14
5 Hash-Join

• Partition both relations using hash fn h R
  tuples in partition i will only match S tuples in
  partition i.

• Read in a partition of R, hash it using h2 (ltgt
  h!). Scan matching partition of S, search for
  matches.

15
Observations on Hash-Join

• partitions k lt B-1 (why?), and B-2 gt size of
  largest partition to be held in memory. Assuming
  uniformly sized partitions, and maximizing k, we
  get
• k B-1, and M/(B-1) lt B-2, i.e., B must be gt
• If we build an in-memory hash table to speed up
  the matching of tuples, a little more memory is
  needed.
• If the hash function does not partition
  uniformly, one or more R partitions may not fit
  in memory. Can apply hash-join technique
  recursively to do the join of this R-partition
  with corresponding S-partition.

16
Cost of Hash-Join

• In partitioning phase, readwrite both relns
  2(MN). In matching phase, read both relns MN
  I/Os.
• In our running example, this is a total of 4500
  I/Os.
• Sort-Merge Join vs. Hash Join
• Given a minimum amount of memory (what is this,
  for each?) both have a cost of 3(MN) I/Os. Hash
  Join superior on this count if relation sizes
  differ greatly. Also, Hash Join shown to be
  highly parallelizable.
• Sort-Merge less sensitive to data skew result is
  sorted.

17
General Join Conditions

• Equalities over several attributes (e.g.,
  R.sidS.sid AND R.rnameS.sname)
• For Index NL, build index on ltsid, snamegt (if S
  is inner) or use existing indexes on sid or
  sname.
• For Sort-Merge and Hash Join, sort/partition on
  combination of the two join columns.
• Inequality conditions (e.g., R.rname lt S.sname)
• For Index NL, need (clustered!) B tree index.
• Range probes on inner matches likely to be
  much higher than for equality joins.
• Hash Join, Sort Merge Join not applicable.
• Block NL quite likely to be the best join method
  here.

18
Summary

• When do we use hash join vs sort-merge join?
• Why hash function should be good for method to
  work?
• Why at least one relation should fit in RAM fully
  (its hash index)?
• How can you improve sort-merge performance in a
  similar way to external sort?
• Why I/O operation count is the most critical in
  querying databases?
• List all join implementations and their
  differences.