Efficient Evaluation Plan for a Query and Estimation of its Cost in Pages

Question

Consider relation Executives(ename, title, dname, address), where all attributes have

the same length and ename is a candidate key. The relation contains 12; 000 pages and the

buffer has 10 pages. Assume that 10% tuples satisfy the following query.

Select E.title, Count(*)

From Executives E

Where E.dname > ’W%’

Group by E.title

For each of the following scenarios, describe the most efficient evaluation plan for the query

and estimate its cost in pages.

(a) Only a clustering B+ tree index on title is available.

(b) Only an unclustered B+ tree index on title is available.

(c) Only a clustered B+ tree index on dname is available.

(d) Only a clustering B+ tree index on (dname, title) is available.

(e) Only a clustering B+ tree index on (title, dname) is available.

Summary

This question belongs to computer science and discusses about most efficient evaluation plan for a query.

Word count: NA