Hi Folks,

Thie query is taking nearly 3-4 mins now - any idea on optimizing it...

select t.empcodea,(to_char(sum(to_number(substr(translate(time_diff,':','.'),1,length(translate(time_diff,':','.'))-3)))+
(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_diff,':','.'))-1,2)))-
Mod(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_diff,':','.'))-1,2))),60))/60)||
'.'||to_char(Mod(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_diff,':','.'))-1,2))),60))),
round(m.avgctc/

(select to_char(sum(to_number(substr(translate(time_diff,':','.'),1,length(translate(time_diff,':','.'))-3)))+
(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_diff,':','.'))-1,2)))-
Mod(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_diff,':','.'))-1,2))),60))/60)||
'.'||to_char(Mod(sum(to_number(substr(translate(time_diff,':','.'),length(translate(time_diff,':','.'))-1,2))),60))
from timesheet t2
where TO_CHAR(T2.CURR_DAY,'YYYYMM')='200407' and t2.empcodea=t.empcodea),2)AVGSAL

from timesheet t,ctcmaster m,cost c
where TO_CHAR(T.CURR_DAY,'YYYYMM')='200407' and t.empcodea in (select distinct t.empcodea from timesheet t where t.client_function='SUN')
and t.client_function='SUN'and c.empcodea=t.empcodea and m.desigcode=(select c.cost from cost c where c.empcodea=t.empcodea)
group by t.empcodea, m.avgctc,'AVGSAL'

Pradhan

Oracle LazyDBA home page