Consider the condition join R1 ??R1.a=R2.b R2, given the following information a
ID: 670638 • Letter: C
Question
Consider the condition join R1 ??R1.a=R2.b R2, given the following information about the relations to be joined. The cost-metric is the number of IOs. The cost of writing the result would be the same independent of the particular join method used, hence we henceforth can ignore it. Given:
R1 has 10,000 tuple, 10 tuples per block • R2 has 2,000 tuple, 10 tuples per block • The available memory buffers are 52
Assume we use a block-oriented nested loop join. a. Which relation you suggest to be the outer relation? b. What is the cost of the join if we use the outer relation as you suggested? c. What is the cost of the join if we use the other relation (not what you suggestion) as the outer one?
Explanation / Answer
a) Hash based relation was my suggestion for outer relation.
b) since each block has 10 tuples..we will create hash size of one...so cost will be 10 + 52(bufferrs available) = 62
c) If not hash based...for ech tuple we have to create relation between them..then cost will be very high.
For R1 ..it will take 10000 x 10 + for R2 2000x10 + 52
10000x10 + 2000x10 + 52