5. Suppose that the following JOIN operation is performed on relations PLAYERS and
PLAYER_PERFORMANCE from the previous questions. PLAYERS has 2000 records stored in 100
disk blocks and PLAYER_PERFORMANCE has 5300 records stored in 300 disk blocks.
Query:
SELECT
*
FROM
WHERE
PLAYERS AS P, PLAYER_PERFORMANCE AS PP
P.PlayerID = PP.PlayerID;
Assume there are 20 main memory buffers available and that the blocking factor for writing the join
result back to disk is 37 records per block. The following access paths are also available:
• A primary index on P.PlayerID, with levels $x_{PlayerID} = 2$.
• A primary index on the composite key (PP.PlayerID, PP. GameID) with levels $x_{(PlayerID, GameID)} = 3$.
• In addition, the relation PP is sorted on PlayerID in ascending order. The PLAYERS table is
also sorted on PlayerID in ascending order.
a. Estimate the cost using the Simple Nested-Loop Join. Assume that one buffer is reserved for
reading from the INNER relation, another one for writing to the output file, and the remaining
for reading from the OUTER relation. Justify your decision on choosing which relation
should be the outer or inner. [5 points]
b. Estimate the cost using the Simple Nested-Loop Join. Assume that one buffer is reserved for
reading from the OUTER relation, another one for writing to the output file, and the
remaining for reading from the INNER relation. Justify your decision on choosing which
relation should be the outer or inner. [5 points]
c. Estimate the cost using the Index-based Nested-Loop Join with the provided available indices.
Justify your decision on choosing which relation should be the outer or inner, and how the
buffers should be best allocated. [10 points]
d. Estimate the cost using the Sort-Merge Join. [10 points]
e. Estimate the cost using the Partition-Hash Join. [10 points]
f. Determine the optimal join method from your computations in options a through e. [5 points]
