(a) Suppose that a clustered B+ tree index ontitleis (the only index) available. Whatis the cost of the best plan?If an additional index (on any search key that youwant) is available, would it help to produce a better plan?(b) Suppose that an unclustered B+ tree index ontitleis (the only index) available.What is the cost of the best plan?(c) Suppose that a clustered B+ tree index ondnameis (the only index) available.What is the cost of the best plan? If an additional index (on any search key thatyou want) is available, would it help to produce a better plan?(d) Suppose that a clustered B+ tree index ondname, titleis (the only index) avail-able. What is the cost of the best plan?(e) Suppose that a clustered B+ tree index ontitle, dnameis (the only index) avail-able. What is the cost of the best plan?Exercise 14.5Consider the queryπA,B,C,D(RA=CS). Suppose that the projection routineis based on sorting and is smart enough to eliminate all but the desired attributes during theinitial pass of the sort, and also to toss out duplicate tuples on-the-ﬂy while sorting, thuseliminating two potential extra passes. Finally, assume that you know the following:R is 10 pages long, and R tuples are 300 bytes long.S is 100 pages long, and S tuples are 500 bytes long.C is a key for S, and A is a key for R.The page size is 1,024 bytes.Each S tuple joins with exactly one R tuple.The combined size of attributes A, B, C, and D is 450 bytes.A and B are in R and have a combined size of 200 bytes; C and D are in S.1. What is the cost of writing out the final result? (As usual, you should ignore this costin answering subsequent questions.)2. Suppose that three buffer pages are available, and the only join method that is imple-mented is simple (page-oriented) nested loops.(a) Compute the cost of doing the projection followed by the join.

408Chapter 14(b) Compute the cost of doing the join followed by the projection.(c) Compute the cost of doing the join first and then the projection on-the-ﬂy.(d) Would your answers change if 11 buffer pages were available?3. Suppose that there are three buffer pages available, and the only join method that isimplemented is block nested loops.(a) Compute the cost of doing the projection followed by the join.(b) Compute the cost of doing the join followed by the projection.(c) Compute the cost of doing the join first and then the projection on-the-ﬂy.(d) Would your answers change if 11 buffer pages were available?Exercise 14.6Brieﬂy answer the following questions.1. Explain the role of relational algebra equivalences in the System R optimizer.2. Consider a relational algebra expression of the formσc(πl(R×S)).Suppose that theequivalent expression with selections and projections pushed as much as possible, takinginto account only relational algebra equivalences, is in one of the following forms.Ineach case give an illustrative example of the selection conditions and the projection lists(c,l,c1,l1, etc.).

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 929 pages?

Upload your study docs or become a

Course Hero member to access this document

Term

Summer

Professor

Mr. Bhattachar

Tags

Relational model