Exercise 19: Drawing Query Execution Plans

Adapted from Exercise 8.11 Ramakrishnan & Gehrke. Consider the following relations:

Emp(eid integer, ename varchar, sal integer, age integer, did integer)
Dept(did integer, budget integer, floor integer, mgr_eid integer)

Salaries range from $10,000 to $100,000, ages vary from 20 to 80, each department has about five employees on average, there are 10 floors, and budgets vary from $10,000 to $1 million. You may assume the following.

• Nested loop joins (NLJ) and index nested loop joins (INLJ) are supported.
• a hash index on Dept.floor has been created
• a B+ tree index on (Emp.age,Emp.salary) has been created
• a B+ tree index on Dept.did has been created

Draw at least 4 different query execution plans for the following query.

SELECT E.eid
FROM Emp E, Dept D
WHERE E.did=D.did AND D.floor=10 AND E.age<30 AND
E.salary>70000