Consider the following table:

The employee table is stored in a heap file organisation which has 500 pages and 100,000 employees evenly distributed among them (i.e 200 tuples per page).

You want to improve the performance of the following query on the table above:

Note that there are 1% of employees whose salary > 200000. Assume that there are 25 pages in the buffer available for the query.

Given the following choices, which one of the options below would you choose? Explain the query plan for each option and also estimate the costs of each query plan in terms of disk I/Os to justify your choice.

 

Option 1: An unclustered B+ tree index on Employee with height 3. Format of data entries is . Note that the number of leaf pages is 32.

Option 2: A dense clustered B+ tree on Employee with height 2. Format of data entries is . Note that the number of leaf pages is 30.

Option 3: No index

help me do this question

 

 

Transcribed Image Text:

CREATE TABLE Employee ( ) go eno ename address position salary dno INT PRIMARY KEY, VARCHAR (255), VARCHAR (255), VARCHAR (100), FLOAT, INT CREATE TABLE Employee ( ) go eno ename address position salary dno INT PRIMARY KEY, VARCHAR (255), VARCHAR (255), VARCHAR (100), FLOAT, INT