Exercise 6: Indexes and Join Algorithms

1. This is the same as Figure 10.16 in the book on page 354.




Circle the pages you would need to read in order to find all entries >= key value 15.
Number the circled pages in the order that you would read them.

2. For this question, use the figure above. Assume that the index is over an attribute called “age”.

1. How many pages do you have to read to identify all rows that satisfy the condition “age = 16”? (Just find the rows, not retrieve them)
2. How many pages do you have to read to identify all rows that satisfy the condition “age < 22”? (Just find the rows, not retrieve them)
3. How many pages do you have to read to identify all rows that satisfy the conditions “age < 22 AND age > 5”? (Just find the rows, not retrieve them)

Now, make the following assumptions:

• The index is dense
• In the table to which this index points, there are three data rows per page

4. If the index is clustered, what is the most number of pages that you will need to read to retrieve the rows from part (c) (based on the addresses you get, e.g., 8*, from the leaf level of the index)?
5. If the index is unclustered, what is the most number of pages that you will need to read to retrieve all rows from part (c) (based on the addresses you get, e.g., 8*, from the leaf level of the index)?

---

3. Suppose I am joining two tables A and B using the join condition A.field1 = B.field1.

Suppose that table A is already sorted into ascending order on field1.
Suppose that table B is already sorted into ascending order on field1.

a. Will the Page-oriented nested loops join algorithm, as shown on slide 2 of lecture 6, work any slower or any faster on these sorted tables than it would if tables A and B were not sorted? (In other words, is the Page-oriented nested loops join algorithm able to exploit the fact that one or both of its input tables are sorted on the join attribute?) Explain briefly.

b. Can you use the page-oriented nested loops algorithm to compute the cross product of two tables A and B? Explain briefly. (For the purpose of this question, you can assume that A and B are not sorted.)

c. Can you use the index-nested loops algorithm to compute the cross product of two tables A and B? Explain briefly. (If you are able to use this algorithm, then explain what sort of index you would use for the table in the inner loop.)

---

4. Challenge question (on your own, if you want to): Devise an algorithm that calculates the DIVIDE operator, and calculate the number of I/O's your algorithm takes. (Do the DIVIDE directly – in a single operator.)