Consider the following query:
SELECT E.EmpName
FROM EMPLOYEE E
JOIN DEPARTMENT D
ON E.DeptID = D.DeptID
WHERE D.DeptName = 'Sales';
Assume that an index exists on DEPARTMENT.DeptName , an index exists on EMPLOYEE.DeptID , and only a small number of departments satisfy the condition. Which strategy is likely to be efficient?

Question

Consider the following query: 
 SELECT E.EmpName 
 FROM EMPLOYEE E 
 JOIN DEPARTMENT D 
    ON E.DeptID = D.DeptID 
 WHERE D.DeptName = 'Sales'; 
 Assume that an index exists on  DEPARTMENT.DeptName , an index exists on  EMPLOYEE.DeptID , and only a small number of departments satisfy the condition. Which strategy is likely to be efficient?

Accepted Answer

Use the index on  DeptName  to find matching departments, then use the  EMPLOYEE.DeptID  index to retrieve matching employees.

Answer

Apply projection before checking the department name and discard all join attributes.

Answer

Scan every employee and every department using a Cartesian product.

Answer

Ignore both indexes and sort both entire relations before performing a Cartesian product.

Crowdly

Consider the following query: SELECT E.EmpName FROM EMPLOYEE E JOIN DEPART...

Want instant access to all verified answers on moovit.vit.ac.in?