- Pipeline for
a binary operator:
- Fact:
- The number of buffers required for input buffering can be very large
In fact:
- The availability of memory buffers will often dictate which implementation algorithm we can use to execute a specific binary (relational algebra) operation !!!
- Consider the following
query plan:
Relevant statistics:
- B(R) = 5000 blocks - clustered, no index on R
- B(S) = 10000 blocks - clustered, no index on S
- B(T) = 10000 blocks - clustered, no index on T
- B(R ⋈ S) = k
(k ≤ 49) blocks
- The result of
B ⋈ S is
pipelined to
⋈2
(⋈2 reads one tuple from B ⋈ S at a time
- The result of
B ⋈ S is
pipelined to
⋈2
- Available buffers = 101 blocks
- Choice
for the join algorithm:
- One-pass join algorithm (see: click here)
- 2-pass join algorithm based on hashing (see: click here)
- Problem:
- Determine which algorithm
you can use for
⋈1 and
⋈2
- Determine the total # disk IOs incurred by the query plan
- Determine which algorithm
you can use for
⋈1 and
⋈2
- Quick reminder:
- One-pass join algorithm
(see:
click here)
# disk IOs = B(S) + B(R)
M ≥ B(S) + 1
- 2-pass join algorithm
based on
hashing
(see:
click here)
# disk IOs = 2(B(R) + B(S)) (for hashing) + (B(R) + B(S)) (for one-pass algorithm)
(M−1)2 ≥ min( B(R), B(S) )
- One-pass join algorithm
(see:
click here)
-
Determine a suitable
join algorithm for
⋈1:
- First, consider whether we can
use one-pass join algorithm for
⋈1:
Since:
M = 101 B(smallest) = 5000 M < B(smallest) + 1 ====> cannot use one-pass algorithm
- Next, consider whether we can
use 2-pass join algorithm for
⋈1:
Computation:
Use 101 buffer to hash R and S into 100 sub-relations Ri and Si Assuming uniform distribution: B(Ri) = 5000/100 = 50 blocks B(Si) = 10000/100 = 100 blocks One pass on each Ri and Si: M = 101 B(smallest) = 50 M ≥ B(smallest) + 1 ====> OK to use 2-pass algorithm
- First, consider whether we can
use one-pass join algorithm for
⋈1:
-
Determine the cost (#disk IOs)
to execute
⋈1:
Hash R: B(R) [read] + B(R) [write] Hash S: B(S) [read] + B(S) [write] 1 pass join on each Ri ⋈ Si: B(R) [read] + B(S) [read] ----------------------------------------------------------------- Total # disk IOs: 3 B(R) + 3 B(S) = 3 × 5000 + 3 × 10000 = 45000 blocks
- Intermezzo:
- It is Phase 2
(= the one-pass join algorithm
on each sub-relation
Ri ⋈ Si)
that produces
output piped
to
⋈2:
- The buffers used are as follows:
- It is Phase 2
(= the one-pass join algorithm
on each sub-relation
Ri ⋈ Si)
that produces
output piped
to
⋈2:
-
Determine a suitable
join algorithm for
⋈2:
- First, consider whether we can
use one-pass join algorithm for
⋈2:
- Because the result is
pipelined,
the operator
⋈2 will
build the
search index
on its
first operand
while
the operator
⋈1
is active:
- According to the information that
k ≤ 49, the
# buffers used for this
search structure will be
< 49 buffers:
- We need to use the 1 buffer
to scan the
relation T:
(Actually, we could release the buffers used by Ri ⋈ Si already --- but I will follow the text book)
- Total # buffers required:
Req. buffers = 50 + 1 + k + 1 = 52 + k k ≤ 49 ≤ 52 + 49 ≤ 101
- Because the result is
pipelined,
the operator
⋈2 will
build the
search index
on its
first operand
while
the operator
⋈1
is active:
- Conclussion:
- OK to use one-pass join algorithm for ⋈2 !!!
- First, consider whether we can
use one-pass join algorithm for
⋈2:
-
Determine the cost (#disk IOs)
to execute
⋈2:
Build index on R ⋈ S: 0 (pipelined !!) Scan T: T(R) --------------------------------------------------- Total # disk IOs: T(R) = 10000
- Total # disk IOs:
# disk IOs incurred by ⋈1 = 45000 # disk IOs incurred by ⋈2 = 10000 ----------------------------------- Total = 55000