Compare the cost of two different query plans for the following query: salary > 40000 select (EMPLOYEE |X| DNO=DNUMBER DEPARTMENT) Use the database statistics in Figure 15.8

What will be an ideal response?

One plan might be for the following query tree







We can use the salary index on EMPLOYEE for the select operation:

The table in Figure 18.8(a) indicates that there are 500 unique salary values, with a low value of 1 and a high value of 500. (It might be in reality that salary is in units of 1000 dollars,

so 1 represents $1000 and 500 represents $500,000.)

The selectivity for (Salary > 400) can be estimated as

(500 - 400)/500 = 1/5

This assumes that salaries are spread evenly across employees.

So the cost (in block accesses) of accessing the index would be

Blevel + (1/5) * (LEAF BLOCKS) = 1 + (1/5)* 50 = 11

Since the index is nonunique, the employees can be stored on any of the data blocks.

So the the number of data blocks to be accessed would be

(1/5) * (NUM_ROWS) = (1/5) * 10,000 = 2000

Since 10,000 rows are stored in 2000 blocks, we have that

2000 rows can be stored in 400 blocks. So the TEMPORARY table (i.e., the result of the selection