Query Optimization Problem Consider the following three relations: R(a,b,c), S(d,e), and W(f,g,h). Assume that R has 100
Posted: Fri Jul 01, 2022 5:34 am
Query Optimization Problem
Consider the following three relations: R(a,b,c), S(d,e), andW(f,g,h). Assume that R has 1000 tuples, S has 10,000 tuples and Whas 100 tuples. Also, assume that each page stores 10 tuples, so Rhas 100 pages, S has 1000 pages, and W has 10 pages. Assume abuffer of 5 pages. Consider now the following SQL Query: SELECT *FROM R, S, W WHERE R.a = S.d AND R.c = W.h Assume that allrelations are stored in heap files, there are no indexes, only(BNL) block nested-loop joins can be used, and the selectivity ofeach join is 0.1%. That is, the join between two tables willproduce 0.1% of the Cartesian product result (the maximum possibleresult between the two tables). Show the query evaluation planselected by a System R based query optimizer. Use the number ofdisk IO operations as the cost function. Hints: 1) Remember that aSystem R optimizer will try to avoid Cartesian products in theplan. So do NOT consider such plans. 2) The System R optimizer willonly consider left-deep plans.
Consider the following three relations: R(a,b,c), S(d,e), andW(f,g,h). Assume that R has 1000 tuples, S has 10,000 tuples and Whas 100 tuples. Also, assume that each page stores 10 tuples, so Rhas 100 pages, S has 1000 pages, and W has 10 pages. Assume abuffer of 5 pages. Consider now the following SQL Query: SELECT *FROM R, S, W WHERE R.a = S.d AND R.c = W.h Assume that allrelations are stored in heap files, there are no indexes, only(BNL) block nested-loop joins can be used, and the selectivity ofeach join is 0.1%. That is, the join between two tables willproduce 0.1% of the Cartesian product result (the maximum possibleresult between the two tables). Show the query evaluation planselected by a System R based query optimizer. Use the number ofdisk IO operations as the cost function. Hints: 1) Remember that aSystem R optimizer will try to avoid Cartesian products in theplan. So do NOT consider such plans. 2) The System R optimizer willonly consider left-deep plans.