Concatenation and Correlated Subqueries - Performance Tuning

Concatenation allows to retrieve data correlated to the MIN/MAX function in a single pass

Example:

1. Employees with highest salary in each department

This can be written as using correlated subquery

SELECT D_No, Sal, L_Na, F_Na
FROM test.employee ee
WHERE Sal IN
(SELECT MAX (Sal)
FROM test.employee em
WHERE ee.D_No=em.D_No
GROUP BY D_No)
ORDER BY D_No;

Answer Set:

Dept_No Salary_Amount Last_Name First_Name
10 70000 pitt           arun     
20 75000 lucas          frank    
30 60000 jones          indiana  
40 65000 phips          carla    
50 60000 stifler        tom      
50 60000 young          nikie    
60 60000 penn           arun     
70 70000 paul           ak       
80 70000 nelson         julie    
90 75000 white          sam      
100 45000 pitt           mark     

It can also be written as

SELECT D_No,
MAX(Sal || ' ' || L_Na || ',' ||F_Na)
FROM test.employee
GROUP BY D_No
ORDER BY D_No ;

Answer Set:

Dept_No Maximum(((((Salary_Amount||' ')||Last_Name)||',')||First_Nam
10       70000 pitt           ,arun     
20       75000 lucas          ,frank    
30       60000 jones          ,indiana  
40       65000 phips          ,carla    
50       60000 young          ,nikie    
60       60000 penn           ,arun     
70       70000 paul           ,ak       
80       70000 nelson         ,julie    
90       75000 white          ,sam      
100       45000 pitt           ,mark     

Did you observe the two answer sets? Can you tell the difference and how to overcome it? Please leave your answers in comments.



Related Posts


0 comments: