2 Operator Algorithms 2. (15 points) Consider two relations R and S with the following sizes: Relation Blocks # Tuples R 100 1000 S 80 800 (a) (5 points) Compute the cost of joining these two relations in each of the following two scenarios: . Using a tuple-at-a-time nested loop join algorithm with R as the outer relation. . Using a page-at-a-time nested loop join algorithm with R as the outer relation. (b) (5 points) Assume the join operator is allowed to use M 10 pages of memory What is the least-cost method that we could use to join the two relations with a nested-loop type of join algorithm? Describe the method for the R and S relations and compute its cost. (c) (5 points) Now consider that the join is a primary-key to foreign-key join and that S is indexed on the join attribute but R is not. Describe how Rand S can be joined using an index-based nested loop join algorithm. Assume that all index pages are in memory. Indicate the cost assuming each tuple of R joins with exactly one tuple in S. 2 Operator Algorithms 2. (15 points) Consider two relations R and S with the following sizes: Relation Blocks # Tuples R 100 1000 S 80 800 (a) (5 points) Compute the cost of joining these two relations in each of the following two scenarios: . Using a tuple-at-a-time nested loop join algorithm with R as the outer relation. . Using a page-at-a-time nested loop join algorithm with R as the outer relation. (b) (5 points) Assume the join operator is allowed to use M 10 pages of memory What is the least-cost method that we could use to join the two relations with a nested-loop type of join algorithm? Describe the method for the R and S relations and compute its cost. (c) (5 points) Now consider that the join is a primary-key to foreign-key join and that S is indexed on the join attribute but R is not. Describe how Rand S can be joined using an index-based nested loop join algorithm. Assume that all index pages are in memory. Indicate the cost assuming each tuple of R joins with exactly one tuple in S