SQL语句优化

 
 帮忙SQL下SQL：select empno, empname, over_startime,over_endtime,
              overtimenum,leave_startime, leave_endtime,leavetimenum,
              sum(coktotals)over(partition by empno) remainnum
          from (
                select e.empno, e.empname,
                        a.starttime over_startime,
                        a.endtime over_endtime,                      
                        '0' leave_startime,
                        '0' leave_endtime,
                        nvl(a.overtimenum, 0) overtimenum, 
                        0 leavetimenum,
                        nvl(f_cokstat_equal_time(a.starttime, a.endtime),0) coktotals
                  from  tmp_employee  e, t_overtime a
                 where  trunc(to_date(a.starttime,'yyyy-mm-dd hh24:mi:ss')) <= to_date(cok_cout_et,'yyyy-mm-dd')
                   and  trunc(to_date(a.endtime,'yyyy-mm-dd hh24:mi:ss'))  >= to_date(cok_cout_st,'yyyy-mm-dd')
                   and  e.empno = a.empno(+)
                   and  exists(
                                 select 1
                                   from t_roleinfo 
                                  where empno = e.empno
                                    and programid = (
                                                      select 1
                                                        from t_programinfo
                                                       where programname = str_programname 
                                                     )
                               )                union all
                select  e.empno,
                        e.empname,
                        '0' over_startime,
                        '0' over_endtime,
                        t.starttime leave_starttime,
                        t.endtime leave_endtime,
                        0 overtimenum,
                        nvl(t.leavetimenum,0) leavetimenum,                         
                        -f_cokstat_record_one(i_cout_st,
                                            i_cout_et,
                                            to_date(substr(t.starttime, 1, 10), 'yyyy-mm-dd'),
                                            to_date(substr(t.endtime, 1, 10), 'yyyy-mm-dd')
                                          ) coktotals
                  from  tmp_employee  e, t_leave t
                 where  trunc(to_date(t.starttime,'yyyy-mm-dd hh24:mi:ss')) <= to_date(cok_cout_et,'yyyy-mm-dd')
                   and  trunc(to_date(t.endtime,'yyyy-mm-dd hh24:mi:ss'))  >= to_date(cok_cout_st,'yyyy-mm-dd')
                   and  e.empno = t.empno(+)
                   and  exists(
                                 select 1
                                   from t_roleinfo 
                                  where empno = e.empno
                                    and programid = (
                                                      select 1
                                                        from t_programinfo
                                                       where programname = str_programname 
                                                     )
                               )
                 );