Slideshow:
Example on selection implementation algorithm for a query plan -
B(R⋈S) medium
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) medium
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) medium
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) medium
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) medium
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) medium
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) medium
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) medium
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) medium
Step 3: check if we can use a 1-pass algorithm for ⋈2:
Therefore: we cannot use the 1-pass join algorithm
Example on selection implementation algorithm for a query plan -
B(R⋈S) medium
Step 4: check if we can use a 2-pass (hashing based) algorithm for ⋈2:
Note: B(R⋈1S) ≤ 5000. Therfore: B(R⋈1S)/50 ≤ 100 (each chunk of the first relation of ⋈2 ≤ 100 blks)
Comment: due to buffer recycling we cannot test for remaining 50 buffer ≥ sqrt( B(R⋈2S) )
Example on selection implementation algorithm for a query plan -
B(R⋈S) medium
Step 4: check if we can use a 2-pass (hashing based) algorithm for ⋈2:
(This is the buffer recylcing step that I referred to in the last slide....)
Example on selection implementation algorithm for a query plan -
B(R⋈S) medium
Step 4: check if we can use a 2-pass (hashing based) algorithm for ⋈2:
(Because otherwise, the tuples with the same join key will be hashed into a different bucket....)
Notice that:
size of each sub-relation of
R⋈1S is
≤ 5000/50 = 100 blks
size of each sub-relation of
T is
= 200 blks
So pass 2 will use chunks of R⋈1S as build relation !
Example on selection implementation algorithm for a query plan -
B(R⋈S) medium
Step 4: check if we can use a 2-pass (hashing based) algorithm for ⋈2:
Example on selection implementation algorithm for a query plan -
B(R⋈S) medium
Cost analysis:
- Consider the following
modified scenario
for
the same
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
-
50 < B(R ⋈ S) ≤ 5000
- 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)
- 3-pass join algorithm based on hashing (see: click here)
- 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:
-
We have performed this
check
before:
Since:
M = 101 < 5001We cannot use a 1-pass algorithm for ⋈1
-
We have performed this
check
before:
- Step 2:
check if we can use
a
2-pass (hashing based)
join algorithm for
⋈2
-
We have performed this
check
before:
How is the ⋈1 executed:
Pass 1 of the 2-pass hash join alg: (1) We use 101 buffer to hash R into 100 sub-relations Ri
(2) 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
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 only use 50 buffers to index Ri
+ 1 buffer to scan Si
-
We have performed this
check
before:
-
Determine the cost (#disk IOs)
to execute
⋈1:
SAME !!!
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:
- We will get a different outcome because B(R ⋈S) > 50 (remaining buffers)
- Check if we can
use the 1-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:
Observe that:
- We have 50 buffers remaining for ⋈2
- B(R ⋈ S) > 50 blocks
- Conclussion:
- We cannot to 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:
- Next:
check if we can
use 2-pass
join algorithm for
⋈2:
- Note:
- The check is performed by running the 2 pass algorithm using M' = 50 buffers !!!
- In pass 1, the
2-pass hash algorithm will use the
remaining
50 (= 101 - (50 + 1)) buffers
as 50 buckets
and
hash
the input tuples
of R ⋈ S onto
disk:
Each chunk Ri has the size B(R⋈S)/50 (≤ 100 blks !!)
After hashing B(R⋈S) (and write chunks to disk), we can free all buffers:
- We must use the
same number (= 50) of
buckets to
hash the
second operand T:
- After
we hash
the relation T:
- The left input sub-relations and the right input sub-relations is stored on disk !!!
Therefore, we can use all 101 buffers for the pass 2 of the hash-based join:
-
Phase 2 of the
hash-based join is the
one-pass
join algorithm
on each fragment pair ("chunk"):
- Each chunk has the size B(R⋈S)/50 which is ≤ 5000/50 = 100 blocks
- We have (M−1) = 100 buffers available !!!
So: 2-pass hash join will work !!!
Conclusion:
- If B(R⋈S) ≤ 5000, then we can use 2-pass hash-based join algorithm for ⋈2
- Note:
-
Cost (#disk IOs)
to execute
⋈2
(assuming
that 50 <
B(R⋈S) ≤ 5000):
Hash (R⋈S): 0 [pipelined read] + B(R ⋈ S) [hash/write Ri] Hash T: 10000 [read] + 10000 [write] Phase 2 hash join (R⋈S) ⋈ T: B(R ⋈ S) [read (R ⋈ S)] + 10000 [read T] ----------------------------------------------------------------------------- Total: 2 × B(R ⋈ S) + 30000
# disk IOs for ⋈1 = 45000 # disk IOs for ⋈2 = 2×B(R ⋈ S) + 30000 ------------------------------------------------------- Total # disk IOs = 2×B(R ⋈ S) + 75000 |