Function 쿼리수행시간 0 2 1,653

by Oracle10g [2016.11.10 17:55:32]


        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로 파라미터를 받아 처리하는 형식이라...

어떤식으로 튜닝을 한다면 시간을 줄일 수 있을까요?

by 우리집아찌 [2016.11.10 22:46:39]

펑션을 빼고 인라인뷰로 만들어서 조인하세요

서브쿼리도 가능하시면 조인으로 바꾸세요

펑션도 올려주세요


by Oracle10g [2016.11.11 16:14:19]

운영 스크립트라 보안정책상 제한되네요 ㅠㅠ

우선은 펑션 한번만써서 나머지는 화면단에서 해결하는걸로 시간 반으로 단축시키고 쓰는중입니다...ㅠㅠㅠ

아찌님이 말씀해주신부분도 반영했습니다 ㅎㅎ답변감사합니다^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입