Assume number of memory buffer pages is 10 or higher

This preview shows page 9 - 13 out of 15 pages.

18. (2 points) Assume number of memory buffer pages is 10 or higher, but the specific valueis unknown. Which simple join method below will have the lowest I/O cost?A. Block-nested loops joinB. Page-oriented loops joinC. Simple-nested loops joinD. None of the above or it cannot be determined.
19. (4 points) For which value of number of buffer pages B will Block-nested-loops join havefewer total page I/Os than Grace Hash Join?
20. (4 points) Assume number of buffer pages is B = 21. What is the estimated cost fora Grace Hash Join on R and S in terms ofpage writesonly. Exclude any page readcount as well as the final page writes for the join result. Give the closest answer.
21. (4 points) Using the same relationsRandSand the same number of buffer pagesB= 21as in the previous question, assume that initiallyRis unsorted andSis already sorted(no sorting required). Which join algorithm will give the lowest total I/O cost?
Page 9
Query OptimizationConsider the following query on Tables S(sid, sname, rating), B(bid, did, bname, color),R(sid, bid, date), Docks(did, oid, dname), Organization(oid, oname).Assume all at-tributes are equal size.SELECT S.snameFROM Sailors S, Reserves R, Boats B, Docks D, Organizations OWHERE S.sid = R.sid AND R.bid = B.bid AND B.did = D.didAND D.oid = O.oid AND S.rating < 5;Assume that the query planner has chosen the following join order (ignore the numberson the edges for now). Selections and projections are not shown in the diagram below– you will need to figure that out. Assume that projections and selections are pushedinside as much as possible so that the tables in the joins are as small as possible.22. (4 points) How many projection operations will be done for the above query in an op-timized query plan for the above join order, assuming we want to make the tables assmall as possible prior to each join?A. 1 or 2B. 3 or 4C. 5 or 6D. 7 or 8E. None of the above
Page 10
23. (2 points) What operation(s) should be done at location 3 in the above query plan?
24. (2 points) Which of the numbered locations would be best to put a projection ontoS.sname?
25. (4 points) Assume that the estimated size of the output result of joining any two rela-tions in the query plan is sum of the sizes of the input relations. Assuming that eachtable is 12 pages in size that are initially on disk. Assume we have 1000 pages of memoryavailable.Further assume that the join order is as implied by the figure above (withprojections and selections added in as needed to optimize the query). What is minimumnumber of I/Os that is feasible to achieve using the most optimized query plan one cancome up with, excluding the cost of final write to answer the query? Use pipelining ifavailable memory permits whenever possible. Choose the closest answer.
Page 11
TransactionsSchedule IT1T2R(A)R(A)W(A)commitR(A)W(B)commitSchedule IIT1T2W(A)R(A)W(A)W(A)commitcommitSchedule IIIT1T2T3R(A)R(B)W(A)commitW(B)R(A)W(A)commitR(B)W(B)commitSchedule IVT1T2R(A)W(B)R(A)W(A)R(B)W(B)commitR(B)W(B)commitWhich of the above schedule(s) satisfy the given properties for the next 4 questions.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 15 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Fall
Professor
STAFF
Tags
Scantron, Query optimizer, Hash join

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture