Slideshow:
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Problem description:
Find the best algorithm to execute ⋈1 and ⋈2 for M = 101 - click to pull out
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Simplified Problem :
Solution method: brute force search for the min. cost algorithms that can operate with M = 101
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Step 1: check if we can use a 1-pass algorithm for ⋈1:
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Step 2: check if we can use a 2-pass (hashing based) algorithm for ⋈1:
Next, we find the best algorithm for ⋈2 (considering the buffer utilization of ⋈1 !)
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Determining the buffer utilization by the ⋈1 execution:
(⋈2 is inactive and will not use any buffers)
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Determining the buffer utilization by the ⋈1 execution:
Next: we run pass 2 of the 2-pass (hashing-based) algorithm (= a 1-pass algorithm on each Ri and Si)
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Determining the buffer utilization by the ⋈1 execution:
Note: pass 2 is run for every chunk Ri and Si and pass 2 will output tuples to ⋈2 (→ becomes active !)
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Cost (so far):
Click on image to pull out (keep running cost)
Next: determine the best suitable join algorithm for ⋈2
Example on selection implementation algorithm for a query plan -
B(R⋈S) medium
Prelude to considering the implementation algorithm for ⋈2:
⋈1 is actively using its buffers to produce tuples for ⋈2:
I.e.: we must find the best algorithm for ⋈2 using M = 101 − 51 = 50 buffers !!
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Step 3: check if we can use a 1-pass algorithm for ⋈2:
Note: I think this solution works for B(R⋈S) ≤ 50; but the text book thinks it's only good for B(R⋈S) ≤ 49
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Step 3: check if we can use a 1-pass algorithm for ⋈2:
Therefore: ⋈2 can use the 1-pass algorithm !
Note: I think this solution works for B(R⋈S) ≤ 50; but the text book thinks it's only good for B(R⋈S) ≤ 49
Example on selection implementation algorithm for a query plan -
B(R⋈S) small
Cost analysis:
Postscript: why I think it should be
B(R⋈S) ≤ 50
You can use all the remianing 50 buffers to build the search index on R ⋈ S:
Postscript: why I think it should be
B(R⋈S) ≤ 50
When ⋈1 is done, you can free up its buffers:
Postscript: why I think it should be
B(R⋈S) ≤ 50
Then use one buffer (from the freed up buffers) to scan the relation T:
- Consider the following
logical 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) ≤ 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: M = 101 blocks
- For brevity,
we limit the
choices
for the join algorithms:
- One-pass join algorithm (see: click here)
- 2-pass join algorithm based on hashing (see: click here)
- 3-pass join algorithm
based on
hashing
(see:
click here)
(You should also consider: zig-zag and nested-loop for completeness -- methodology will be the same)
- Problem:
- Determine
the minimum cost algorithm
you can use for
⋈1 and
⋈2
- Determine the total # disk IOs incurred by the physical query plan
- Determine
the minimum cost algorithm
you can use for
⋈1 and
⋈2
- Step 1:
check if
we can use a 1-pass algorithm
for ⋈1:
- Since
B(S) = 5000 blocks,
if we
use the one-pass
join algorithm for
⋈1,
we will need 5001 buffers:
Since:
M = 101 < 5001We cannot use a 1-pass algorithm for ⋈1
- Since
B(S) = 5000 blocks,
if we
use the one-pass
join algorithm for
⋈1,
we will need 5001 buffers:
- Step 2:
check if we can use
a
2-pass (hashing based)
join algorithm for
⋈2:
- The 2-pass (hashing based)
join algorithm for
⋈1 will need to use:
- M ≥
(sqrt(B(R)) + 1)
= (sqrt(5000) + 1) = (71+1)
= 72 buffers (M = 101)
Graphically:
- M ≥
(sqrt(B(R)) + 1)
-
Next: determine
buffer utilization for
the ⋈1
execution:
To run pass 1 of the 2-pass hash join alg: (1) We use 101 buffer to hash R into 100 sub-relations Ri
(2) We use 101 buffer to hash S into 100 sub-relations Si
Assuming uniform distribution:
B(Ri) = 5000/100 = 50 blocks (smaller)
B(Si) = 10000/100 = 100 blocks
To run pass 2 of the 2-pass hash join alg: Perform a 1-pass join alg on each Ri and Si:
Because B(Ri) = 50 ---> We will use 50 buffers to index Ri
+ 1 buffer to scan Si
- The 2-pass (hashing based)
join algorithm for
⋈1 will need to use:
-
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
-
Step 3:
determine the best suitable
join algorithm for
⋈2
- Check if we can
use the 1-pass
join algorithm for
⋈2:
- Because the result
R⋈S is
pipelined,
the operator
⋈2 will
build the
search index
on its
first operand
while
the operator
⋈1
is actively outputing tuples:
The 51 (= 50+1) buffers used for ⋈1 will remain allocated
- According
to the given information,
we have
B(R⋈S) ≤ 49
Therefore, the # buffers used for search (hash) structure for R⋈S will be ≤ 49 buffers:
All the (100) fragment joins Ri ⋈ Si of the build relation input can be hashed into a search data structure (of the 1-pass algorithm) using the 49 (remaining) buffers
Note:
- After
processing
all the
(100) fragment joins
Ri ⋈ Si,
we can free up the
buffers for
Ri and
Si
The text book does not do this
I will follow the text book's explanation and include the 1 buffer for T in the requirement
Technically, you do not need the extra (1) buffer for T....
(See next)
- After
processing
all the
(100) fragment joins
Ri ⋈ Si,
we can free up the
buffers for
Ri and
Si
- We allocate
1 buffer
to scan the
relation T:
- Total # buffers
required to
execute
⋈2 using
a 1-pass join:
Req. buffers = 50 + 1 + B(R⋈S) + 1 = 52 + B(R⋈S) B(R⋈S)= k ≤ 49 ≤ 52 + 49 ≤ 101 !!!
- Because the result
R⋈S is
pipelined,
the operator
⋈2 will
build the
search index
on its
first operand
while
the operator
⋈1
is actively outputing tuples:
- Conclussion:
- We can use the 1-pass join algorithm to execute ⋈2 !!!
-
Cost (#disk IOs)
to execute
⋈2:
Build index on R ⋈ S: 0 (pipelined !!) Scan T: B(R) --------------------------------------------------- Total # disk IOs: B(R) = 10000 blks
# disk IOs incurred by ⋈1 = 45000 (2-pass hashing) # disk IOs incurred by ⋈2 = 10000 (1-pass) ----------------------------------- Total = 55000 |