Question

Consider the natural join of the relation R(A,B) and S(A,C) on attribute A. Neither relations have any indexes built on them. Assume that R and S have 80,000 and 20,000 blocks, respectively. The cost of a join is the number of its block I/Os accesses. If the algorithms need to sort the relations, they must use two-pass multi-way merge sort.

Assume that there are 110,000 blocks available in the main memory. We like to have the output sorted based on the join attribute. What is the fastest join algorithm to compute the join of R and S? What is the cost of this algorithm?

Answer 1

Hi,

Answer:

Given: R(A, B) AND R(A, C) relations are natural joins.

sort-merge:
This join algorithm is used for implementing the relational database and for every distinct value of the attribute and the set of the tuples through which every relation to be displayed.

Hash-join:
Hash join algorithm is used for implementing the nested loops joins except the probe side of the joins which can be very small.

Solution-:

Now if there are 110,000 blocks available in the main memory.
Lets consider M = 110,000.
As we should know that the Hash-join and the optimized sort-merge join are the fastest algorithm in the given setting and have equal costs.

The Hash-join should be required that B(R) + B(S) <= M^2.
But B(R) + B(S) is not smaller than M^2.So, we cannot optimize the hash-join algorithm

Now apply the sort-merge join :
B(R) + B(S) >= M^2
As we know that B(R) + B(S) is greater than the M^2. So, we can use an optimized sort-merge algorithm.

Hence, the sort-merge join is the fastest algorithm for computing the join of R and S.

And, the cost this sort-merge join will be:
5(R blocks + S blocks)
5 * (80,000 + 20,000)
5 * 10,000
50,000

Hence the cost of the algorithm = 50,000