136 suppose that a b tree index on building is

Info icon This preview shows pages 5–7. Sign up to view the full content.

View Full Document Right Arrow Icon
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. H9268 ¬ ( building < Watson ) ( department ) b. H9268 ¬ ( building = Watson ) ( department ) c. H9268 ¬ ( building < Watson budget < 50000) ( department ) Answer: a. Use the index to locate the first tuple whose building field has value Watson . From this tuple, follow the pointer chains till the end, retrieving all the tuples. b. For this query, the index serves no purpose. We can scan the file sequentially and select all tuples whose building field is anything other than Watson . c. This query is equivalent to the query: H9268 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 first 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. Find out what plan is generated for this query on the database system you use.
Image of page 5

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

View Full Document Right Arrow Icon
6 Chapter 13 Query Optimization b. Some database systems would use a (block) nested-loop join for this query, which can be very inefficient. Briefly explain how hash-join or merge-join can be used for this query. Answer: a. First create relations r and s , and add some tuples to the two re- lations, before finding 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 P ostgre SQL , 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 r . A and s . A . The hash or merge join algorithms can then be used unchanged. 13.8 Give conditions under which the following expressions are equivalent A , B G agg ( C ) ( E 1 a49 E 2 ) and ( A G agg ( C ) ( E 1 )) a49 E 2 where agg denotes any aggregation operation. How can the above condi- tions be relaxed if agg is one of min or max ? Answer: The above expressions are equivalent provided E 2 contains only attributes A and B , with A as the primary key (so there are no duplicates). It is OK if E 2 does not contain some A values that exist in the result of E 1 , since such values will get filtered out in either expression. However, if there are duplicate values in E 2 . A , the aggregate results in the two cases would be different.
Image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern