Slideshow:
Assumption (system parameters)
I.e.: the efficient one-pass algorithm cannot be used due to buffer availability constraint violation.
Block-base nested-loop R ⋈ S
algorithm -
overview
Relation R will be scanned B(S)/(M−1) times !!
Block-base nested-loop R ⋈ S
algorithm
Relation R will be scanned B(S)/(M−1) times !!
Block-base nested-loop R ⋈ S
algorithm
- Example
Phase 1-1: read M−1 blocks from smaller relation S (and create search structure)
Block-base nested-loop R ⋈ S
algorithm
- Example
Phase 2-1: use 1 buffer to scan relation R and join with S's tuples in (M−1) buffers
Block-base nested-loop R ⋈ S
algorithm
- Example
Phase 1-2: read another M−1 blocks from smaller relation S (and create search structure)
Block-base nested-loop R ⋈ S
algorithm
- Example
Phase 2-2: use 1 buffer to scan relation R and join with S's tuples in (M−1) buffers
Cost of the
Block-based Nested-Loop Join
(R ⋈ S) Algorithm
Buffer requirements of the
Block-based Nested-Loop Join
(R ⋈ S)
Algorithm
Minimim buffer requirement: M ≥ 2 !!!
However: using M = 2 buffers will require B(S) + B(S)×B(R) number of disk IOs !!!
Assymmetric cost measure of
the
Block-based Nested-Loop Join
(R ⋈ S)
Algorithm
Cost of algorithm using relation S as outer relation in nested-loop:
Assymmetric cost measure of
the
Block-based Nested-Loop Join
(R ⋈ S)
Algorithm
Cost of algorithm using relation R as outer relation in nested-loop:
- Assumption:
- There are M buffers
available
- B(S) ≤ B(R)
<< M
Neither relation (R nor S) will fit in the available buffers
- There are M buffers
available
- Block-based
nested loop join
algorithm
in a nutshell:
- The block-based
nested-loop
join algorithm
accesses the
chunks of
(M−1) blocks of
S
For each chunk:
- Scan relation R one time completely and find matching (join) tuples
- The block-based
nested-loop
join algorithm
accesses the
chunks of
(M−1) blocks of
S
- Organization:
- Use (M − 1) buffers
to read and
index
data blocks from
the smaller relation S
- Use 1 buffer to read data blocks from the larger relation R and compute the Join result
- Use (M − 1) buffers
to read and
index
data blocks from
the smaller relation S
- The "block-based"
nested-loop join algorithm:
while ( S ≠ empty ) { Read M - 1 blocks of S: organize these tuples into a search structure (e.g., hash table)' Rewind R; while ( R ≠ empty ) { Read 1 block (b) of R; for ( each tuple t ∈ block b ) do { Find the tuples s1, s2, ... of S (in the search structure) that join with t Output (t,s1), (t,s2), ... } } }Graphically:
- Step 1-1: read
(M−1) blocks
of the
smaller relation
S and
hash the
tuples:
- Step 1-2:
read the
entire relation R and
compute
R ⋈ S1 (fragment 1 of S):
Use the hash table to find the tuples of S !!!
- Step 2-1: read the
next
(M−1) blocks
of the
smaller relation
S and
hash the
tuples:
- Step 2-2:
read the
entire relation R and
compute
R ⋈ S2 (fragment 2 of S):
Use the hash table to find the tuples of S !!!
- Repeat until relation S is processed completely
- Step 1-1: read
(M−1) blocks
of the
smaller relation
S and
hash the
tuples:
- # disk I/O used:
(1) The algorithm will read S once: # disk I/Os = B(S) (2) # fragments Si read: B(S)/(M−1) times For each fragment Si, algorithm read R once # disk I/Os = B(S)/(M−1) × B(R)
So: B(S) Total cost = B(S) + ------- B(R) M-1
- Memory requirement:
- M ≥ 2 buffer
- M ≥ 2 buffer
- Cost when
using smaller
relation S in
the outer loop:
(Presented previously !!!)
(1) The algorithm will read S once: # disk I/Os = B(S) (2) # fragments Si read: B(S)/(M−1) times For each fragment Si, algorithm read R once # disk I/Os = B(S)/(M−1) × B(R)
So: B(S) Total cost = B(S) + ------- B(R) M-1
- Cost when
using larger
relation R in
the outer loop:
(Presented previously !!!)
(1) The algorithm will read R once: # disk I/Os = B(R) (2) # fragments Ri read: B(R)/(M−1) times For each fragment Ri, algorithm read S once # disk I/Os = B(R)/(M−1) × B(S)
So: B(S) Total cost = B(R) + ------- B(R) M-1