Optimize the execution plan of the following query using rule-based optimization

Question

Optimize the execution plan of the following query using rule-based optimization.

SELECT P.number, D.num, E.lname

FROM EMPLOYEE E, DEPARTMENT D, PROJECT P

WHERE P.location = ‘Dammam’ AND E.sex = ‘M’ AND D.num = E.num AND D.mgr_ssn = E.ssn;

Answer 1

Create non clustered index on EMPLOYEE.num

Create non clustered index on EMPLOYEE.ssn

Create non clustered index on DEPARTMENT.num

Create non clustered index on DEPARTMENT.mgr_ssn