SELECT A.PAYYM as PYYM , A.EMPNO , (SELECT KORNM FROM ER_EMPLOYE WHERE EMPNO = A.EMPNO) KORNM , (select comcdnm from zz_comcd where divcd ='OcptypeCd' and comcd = b.ocptypecd) OCPTYPECD , (select comcdnm from zz_comcd where divcd ='OcpgrdCd' and comcd = b.ocpgrdcd) ocpgrdcd , (select comcdnm from zz_comcd where divcd ='OcpgrdCd' and comcd = b.OCPGRDTTMTCD) OCPGRDTTMTCD , max(b.slrystpcd) slrystpcd , substr(max(b.cnssvcyy),1,2)||'-'||substr(max(b.cnssvcyy),3,4) cnssvcyy , max(b.ENTYMD) ENTYMD , nvl(c.OVTMTIME, 0) as OVTMTIME , nvl(c.NIGHTTIME, 0) as NIGHTTIME , nvl(c.HOLYDAYS, 0) as HOLYDAYS , nvl(c.BASEPAYAMT, 0) as BASEPAYAMT , nvl(c.OVTMAMT, 0) as OVTMAMT , nvl(c.NIGHTAMT, 0) as NIGHTAMT , nvl(c.HOLYAMT, 0) as HOLYAMT , nvl(c.OVTMAMT, 0) + nvl(c.NIGHTAMT, 0) + nvl(c.HOLYAMT, 0) as TOT , FN_RETRODNRPAY(c.EMPNO, to_char(add_months(to_date(a.PAYYM, 'yyyyMM'), 12), 'yyyyMM')||'01') as SBASEPAYAMT , NVL(SUM(DECODE(PAYDDCTITEMCD, '1020007', PAYDDCTAMT)), 0) AS SOVTMAMT , NVL(SUM(DECODE(PAYDDCTITEMCD, '1020009', PAYDDCTAMT)), 0) AS SNIGHTAMT , NVL(SUM(DECODE(PAYDDCTITEMCD, '1020008', PAYDDCTAMT)), 0) AS SHOLYAMT , NVL(SUM(DECODE(PAYDDCTITEMCD, '1020007', PAYDDCTAMT)), 0) + NVL(SUM(DECODE(PAYDDCTITEMCD, '1020009', PAYDDCTAMT)), 0) + NVL(SUM(DECODE(PAYDDCTITEMCD, '1020008', PAYDDCTAMT)), 0) AS STOT , FN_RETRODNRPAY(c.EMPNO, to_char(add_months(to_date(a.PAYYM, 'yyyyMM'), 12), 'yyyyMM')||'01') - nvl(c.BASEPAYAMT, 0) as DBASEPAYAMT , NVL(SUM(DECODE(PAYDDCTITEMCD, '1020007', PAYDDCTAMT)), 0) - nvl(c.OVTMAMT, 0) AS DOVTMAMT , NVL(SUM(DECODE(PAYDDCTITEMCD, '1020009', PAYDDCTAMT)), 0) - nvl(c.NIGHTAMT, 0) AS DNIGHTAMT , NVL(SUM(DECODE(PAYDDCTITEMCD, '1020008', PAYDDCTAMT)), 0) - nvl(c.HOLYAMT, 0) AS DHOLYAMT , (NVL(SUM(DECODE(PAYDDCTITEMCD, '1020007', PAYDDCTAMT)), 0) + NVL(SUM(DECODE(PAYDDCTITEMCD, '1020009', PAYDDCTAMT)), 0) + NVL(SUM(DECODE(PAYDDCTITEMCD, '1020008', PAYDDCTAMT)), 0)) - (nvl(c.OVTMAMT, 0) + nvl(c.NIGHTAMT, 0) + nvl(c.HOLYAMT, 0)) AS DTOT , c.NOTE FROM ER_MMPAYDDCTITEMDTL_R A , (select * from ER_MMPAYMST where PYYM <> '201601' union select * from ER_MMPAYMST_JJ) B , (select MMDUTYYYMM ,EMPNO ,DEPTCD ,sum(OVTMTIME) OVTMTIME ,sum(OVTMAMT) OVTMAMT ,sum(NIGHTTIME) NIGHTTIME ,sum(NIGHTAMT) NIGHTAMT ,sum(HOLYDAYS) HOLYDAYS ,sum(HOLYAMT) HOLYAMT ,sum(MNMLDAYS) MNMLDAYS ,sum(MNMLAMT) MNMLAMT ,max(BASEPAYAMT) BASEPAYAMT ,max(NOTE) NOTE from ER_MMPAYDUTYS group by MMDUTYYYMM,EMPNO,DEPTCD) C , ER_OVERWORKMMTOT D WHERE A.EMPNO = B.EMPNO and c.EMPNO = a.EMPNO AND A.PAYYM = B.PYYM and to_char(add_months(to_date(c.MMDUTYYYMM, 'yyyyMM'), 1), 'yyyyMM') = a.PAYYM and c.MMDUTYYYMM is not null and (c.MMDUTYYYMM <> '201602' or c.EMPNO <> '214288') --예외처리 and d.EXCSDUTTOTFLAG = 'X' and d.EMPNO = c.EMPNO and d.OVERWORKYM = c.MMDUTYYYMM and d.OCPTYPECD = b.OCPTYPECD and a.empno = nvl(&i_EMPNO,a.empno) and a.pAyym between &i_frymd and &i_toymd AND B.OCPTYPECD = (select ocptypecd from er_employe where empno =b.empno and stategb <> 'A04') and b.ENTYMD = (select ENTYMD from er_employe where empno = b.empno and stategb <> 'A04') AND A.PAYPYFLAG = B.PAYPYFLAG and b.PAYPYFLAg = '1' and (b.payclsprfshipflag = NVL(&i_hobong,b.payclsprfshipflag) AND A.EMPNO = (SELECT EMPNO FROM ER_EMPLOYE WHERE OCPGRDCD = nvl(&i_OCPGRD,OCPGRDCD) AND EMPNO = A.EMPNO)) and b.payclsprfshipflag not in ('2','9','8') GROUP BY A.PAYYM,A.EMPNO,B.OCPTYPECD,ocpgrdcd,OCPGRDTTMTCD ,c.OVTMTIME,c.NIGHTTIME,c.HOLYDAYS,c.BASEPAYAMT,c.OVTMAMT,c.NIGHTAMT,c.HOLYAMT,c.EMPNO,c.MMDUTYYYMM,c.NOTE ORDER BY PYYM,EMPNO ;
안녕하십니까 여러분~~하루가 또 끝이났네요 ^^
한가지 여쭤보고 싶은게 있어서 질문올립니다...
이 쿼리 실행시간이 750초가 나옵니다...
펑션만 빼면 몇천건의 데이터가 2초~3초이내에 나오거든요 ㅠㅠ
사원번호를 입력한 후 펑션만 따로 돌리면 또 1초이내로 결과가 나옵니다..
아마도 엄청 무거운 펑션(통상임금 계산로직) 을 Row수만큼 두번씩 왔다 갔다 해야하는게 부담인것 같은데...
변수로 담으려 해도 FROM~TO로 파라미터를 받아 처리하는 형식이라...
어떤식으로 튜닝을 한다면 시간을 줄일 수 있을까요?