Consider two relations called Invoice and Customers. Imagine that the Customers relation has 1,000 pages and...

 

Transcribed Image Text:

Consider two relations called Invoice and Customers. Imagine that the Customers relation has 1,000 pages and the Invoice relation has 5,000 pages. Consider the following SQL statement: SELECT * FROM Customers INNER JOIN Invoice ON Customers. Cust_ID = Invoice.Cust_ID; We wish to evaluate an equijoin between Customers and Invoice, with an equality condition Customers. Cust_ID = Invoice.Cust_ID. There are 502 buffer pages available in memory for this operation. Both relations are stored as (unsorted) heap files. Neither relation has any indexes built on it. Consider the alternative join strategies described below and calculate the cost of each alternative. Evaluate the algorithms using the number of disk I/O's (i.e. pages) as the cost. For each strategy, provide the formulae you use to calculate your cost estimates. a) Page-oriented nested loops join. Consider Customers as the outer relation. (1 mark) b) Block-oriented nested loops join. Consider Customers as the outer relation. (1 mark) c) Sort-Merge join. Assume that Sort-Merge Join can be done in 2 passes. (1 mark) d) Hash Join (1 mark) e) What would be the lowest possible cost to perform this query, assuming that no indexes are built on any of the two relations, and assuming that sufficient buffer space is available? What would be the minimum buffer size required to achieve this cost? Explain briefly. (1 mark) Consider two relations called Invoice and Customers. Imagine that the Customers relation has 1,000 pages and the Invoice relation has 5,000 pages. Consider the following SQL statement: SELECT * FROM Customers INNER JOIN Invoice ON Customers. Cust_ID = Invoice.Cust_ID; We wish to evaluate an equijoin between Customers and Invoice, with an equality condition Customers. Cust_ID = Invoice.Cust_ID. There are 502 buffer pages available in memory for this operation. Both relations are stored as (unsorted) heap files. Neither relation has any indexes built on it. Consider the alternative join strategies described below and calculate the cost of each alternative. Evaluate the algorithms using the number of disk I/O's (i.e. pages) as the cost. For each strategy, provide the formulae you use to calculate your cost estimates. a) Page-oriented nested loops join. Consider Customers as the outer relation. (1 mark) b) Block-oriented nested loops join. Consider Customers as the outer relation. (1 mark) c) Sort-Merge join. Assume that Sort-Merge Join can be done in 2 passes. (1 mark) d) Hash Join (1 mark) e) What would be the lowest possible cost to perform this query, assuming that no indexes are built on any of the two relations, and assuming that sufficient buffer space is available? What would be the minimum buffer size required to achieve this cost? Explain briefly. (1 mark)