Correlated Subquery - Performance Tuning

A correlated subquery (CS) is a subquery whose outer query results are processed a row at a time, against the subquery result. The subquery result is computed for each row processed.

• a CS eliminates the need for intermediate or temporary tables
• a CS is fully integrated with global join planning to minimize costs
• CS is significantly faster than the query using temporary tables

Example:

1. Employee with highest salary in each department

SELECT L_Na,Sal, D_No
FROM test.employee ee
WHERE Sal = (SELECT MAX (Sal)
FROM test.employee em
WHERE ee.D_No=em.D_No);

Answer Set:

Last_Name	Salary_Amount	Dept_No
stifler	60000	50
pitt	70000	10
young	60000	50
jones	60000	30
paul	70000	70
penn	60000	60
white	75000	90
nelson	70000	80
pitt	45000	100
lucas	75000	20
phips	65000	40

2. Employees whose salary is greater than the department average salary

SELECT L_Na, Sal, D_No
FROM test.employee ee
WHERE Sal > (SELECT AVG (Sal)
FROM test.employee em
WHERE ee.D_No= em.D_No);

Answer Set:

Last_Name	Salary_Amount	Dept_No
paul	70000	70
phips	65000	40
shook	65000	10
pitt	70000	10
jones	60000	30
lucas	75000	20
wall	65000	10

Related Posts