Has 1500 tuples and r 3 has 750 exercises 5 tuples

Info iconThis preview shows pages 4–7. Sign up to view the full content.

View Full Document Right Arrow Icon
has 1500 tuples, and r 3 has 750
Background image of page 4

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Exercises 5 tuples. Estimate the size of r 1 a r 2 a r 3 and give an efFcient strategy for computing the join. Answer: The estimated size of the relation can be determined by cal- culating the average number of tuples which would be joined with each tuple of the second relation. In this case, for each tuple in r 1 , 1500/ V ( C , r 2 ) = 15/11 tuples (on the average) of r 2 would join with it. The intermedi- ate relation would have 15000/11 tuples. This relation is joined with r 3 to yield a result of approximately 10,227 tuples (15000/11 × 750/100 = 10227). A good strategy should join r 1 and r 2 Frst, since the intermediate relation is about the same size as r 1 or r 2 . Then r 3 is joined to this result. 13.6 Suppose that a B + -tree index on building is available on relation department , and that no other index is available. What would be the best way to handle the following selections that involve negation? a. H ¬ ( building < Watson ) ( department ) b. H ¬ ( building = Watson ) ( department ) c. H ¬ ( building < Watson budget < 50000) ( department ) Answer: a. Use the index to locate the Frst tuple whose building Feld has value Watson . ±rom this tuple, follow the pointer chains till the end, retrieving all the tuples. b. ±or this query, the index serves no purpose. We can scan the Fle sequentially and select all tuples whose building Feld is anything other than Watson . c. This query is equivalent to the query: H building ’Watson’ budget < 5000) ( department ). Using the building index, we can retrieve all tuples with building value greater than or equal to Watson by following the pointer chains from the Frst Watson tuple. We also apply the additional criteria of budget < 5000 on every tuple. 13.7 Consider the query: select * from r , s where upper( r . A ) = upper( s . A ); where upper is a function that returns its input argument with all low- ercase letters replaced by the corresponding uppercase letters. a. ±indoutwhatplanisgeneratedforthisqueryonthedatabasesystem you use.
Background image of page 5
6 Chapter 13 Query Optimization b. Some database systems would use a (block) nested-loop join for this query, which can be very inefFcient. Brie±y explain how hash-join or merge-join can be used for this query. Answer: a. ²irst create relations r and s , and add some tuples to the two re- lations, before Fnding the plan chosen; or use existing relations in place of r and s . Compare the chosen plan with the plan chosen for a query directly equating r . A = s . B . Check the estimated statistics too. Some databases may give the same plan, but with vastly different statistics. (On PostgreSQL, we found that the optimizer used the merge join plan described in the answer to the next part of this question.) b. To use hash join, hashing should be done after applying the upper() function to r . A and s . A . Similarly, for merge join, the relations should be sorted on the result of applying the upper() function on
Background image of page 6

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page4 / 10

has 1500 tuples and r 3 has 750 Exercises 5 tuples Estimate...

This preview shows document pages 4 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online