Cost Estimation 

For the two RA trees below, estimate the join cost assuming the query optimizer chooses a hash join. 

 

For each RA tree, please:

• Give the estimated cost of the join
• State which relation on which you would build the hash table
• For b., describe how you estimated the size of the right input to the join

 

Assume that the number of buffer pages available to the join is 75; that is BP = 75 (BP is the same as B). Please use the numbers in the relation_info table for your calculations.

  
NOTE: for simplicity you may assume that in the players table there is one person named 'Jacob' and then calculate the cost for join algorithm. And if you have done other ways, please include reasoning to your choice.

Transcribed Image Text:

relationname numtuples 6,000 100,000 attribute_info: Relationname attrname Players Players Players Players Sports Players Calories 80 4 relationname -- the name of the relation numtuples -- is the number of tuples in the relation numpages -- is the number of pages in the relation tuplesperpage -- the average number of tuples on each page id name sport numpages age 600 5,000 tuplesperpage 1 10 1 1 20 minvalue maxvalue 100,000 20 6000 100 distinctvalues 100,000 98,000 6000 100 relationname numtuples 6,000 100,000 attribute_info: Relationname attrname Players Players Players Players Sports Players Calories 80 4 relationname -- the name of the relation numtuples -- is the number of tuples in the relation numpages -- is the number of pages in the relation tuplesperpage -- the average number of tuples on each page id name sport numpages age 600 5,000 tuplesperpage 1 10 1 1 20 minvalue maxvalue 100,000 20 6000 100 distinctvalues 100,000 98,000 6000 100

Answer rating: 100% (QA)

The first step in estimating the cost of a hash join is to determine the size of the relation on which we will build the hash table and which is the b... View the full answer