Problem description:

Find the best algorithm to execute ⋈₁ and ⋈₂ for M = 101 - click to pull out

Simplified Problem :

Solution method:   brute force search for the min. cost algorithms that can operate with M = 101

Step 1: check if we can use a 1-pass algorithm for ⋈₁:

Step 2: check if we can use a 2-pass (hashing based) algorithm for ⋈₁:

Next, we find the best algorithm for ⋈₂ (considering the buffer utilization of ⋈₁ !)

Determining the buffer utilization by the ⋈₁ execution:

(⋈₂ is inactive and will not use any buffers)

Determining the buffer utilization by the ⋈₁ execution:

Next:   we run pass 2 of the 2-pass (hashing-based) algorithm (= a 1-pass algorithm on each R_i and S_i)

Determining the buffer utilization by the ⋈₁ execution:

Note:   pass 2 is run for every chunk R_i and S_i and pass 2 will output tuples to ⋈₂ (→ becomes active !)

Cost (so far):

Click on image to pull out (keep running cost)

Next:   determine the best suitable join algorithm for ⋈₂

Prelude to considering the implementation algorithm for ⋈₂:

⋈₁ is actively using its buffers to produce tuples for ⋈₂:

I.e.: we must find the best algorithm for ⋈₂ using M = 101 − 51 = 50 buffers !!

Step 3: check if we can use a 1-pass algorithm for ⋈₂:

Therefore:   we cannot use the 1-pass join algorithm

Step 4: check if we can use a 2-pass (hashing based) algorithm for ⋈₂:

Note:   B(R⋈₁S) ≤ 5000.     Therfore: B(R⋈₁S)/50 ≤ 100 (each chunk of the first relation of ⋈₂ ≤ 100 blks)

Comment:   due to buffer recycling we cannot test for remaining 50 buffer ≥ sqrt( B(R⋈₂S) )

Step 4: check if we can use a 2-pass (hashing based) algorithm for ⋈₂:

(This is the buffer recylcing step that I referred to in the last slide....)

Step 4: check if we can use a 2-pass (hashing based) algorithm for ⋈₂:

(Because otherwise, the tuples with the same join key will be hashed into a different bucket....)

Step 4: check if we can use a 2-pass (hashing based) algorithm for ⋈₂:

Cost analysis: