Improving performance of this view.

From: Frampton Steve R <3srf_at_qlink.queensu.ca>
Date: 18 Oct 1994 18:28:40 GMT
Message-ID: <38144o$ir_at_knot.queensu.ca>

Hello:

I have the following view defined:

select pos.employee_id, pos.location_code, pos.emp_group_code,

       pos.absn_group_code, rtrim(substr(emp.surname,1,20))||' '||
       rtrim(substr(emp.first_name,1,15))||' '||
       substr(emp.second_name,1,1)||
       decode(emp.second_name,NULL,' ','.'),
       sum(pos.actual_hours_week), sum(pos.actual_fte),
       sum(pos.actual_hours_week/5),
       sum(nvl(pos.credit_loss_per_day,pos.actual_fte)),
       job.description_abbr

from ec_employee emp, ec_employee_positions pos, ec_jobs job

where  emp.employee_id = pos.employee_id
and    pos.emp_group_code not like 'TEMCAS%'
and    pos.emp_group_code not like 'SUPTEAC%'
and    pos.emp_group_code not like 'TMLOA_T'
and    pos.emp_group_code not in ('MARKERS','RETIREES','TRUSTEES')
and    pos.position_start_date =
                (select max(tst.position_start_date)
                 from   ec_employee_positions tst
                 where  tst.employee_id = pos.employee_id
                 and    tst.location_code = pos.location_code
                 and    tst.emp_group_code = pos.emp_group_code
                 and    tst.position_start_date <= to_char(sysdate,'YYMMDD')
                 and    nvl(tst.position_end_date,to_char(sysdate,'YYMMDD'))


                                           >= to_char((sysdate-31),'YYMMDD')


                 and    tst.record_status = 'A')
and    nvl(pos.position_end_date,to_char(sysdate,'YYMMDD')) >=
                                         to_char((sysdate-31),'YYMMDD')

and pos.record_status = 'A'
and pos.job_code = job.job_code
group by pos.employee_id, pos.location_code, pos.emp_group_code,

       pos.absn_group_code, emp.surname, emp.first_name,
       emp.second_name, job.description_abbr;

This view is a real workhorse for me (I use it in a variety of applications) but runs a little slower than I'd like. I would like to improve the performance of this view somewhat.

Is there something I can do, or can ask my DBA to do, to make this run a little quicker? Please don't ask me to simplify my view (it is as complicated as it is because of several payroll-related considerations) but if you see where it could be coded more efficiently please let me know.

Any information would be greatly appreciated. Thanks in advance! Received on Tue Oct 18 1994 - 19:28:40 CET