꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 튜닝 질문드립니다. 통계 쿼리

by 만년설 쿼리 튜닝 최적화 오라클 sql [2015.06.12 09:17:22]

select dpt.code,
        dpt.p2009 as p1, to_char(nvl(dpt.a2009/ decode(dpt.p2009, 0, null, dpt.p2009), 0), 'FM90.00') as a1,
        dpt.p2010 as p2, to_char(nvl(dpt.a2010/ decode(dpt.p2010, 0, null, dpt.p2010), 0), 'FM90.00') as a2,
        dpt.p2011 as p3, to_char(nvl(dpt.a2011/ decode(dpt.p2011, 0, null, dpt.p2011), 0), 'FM90.00') as a3,
        dpt.p2012 as p4, to_char(nvl(dpt.a2012/ decode(dpt.p2012, 0, null, dpt.p2012), 0), 'FM90.00') as a4,
        dpt.p2013 as p5, to_char(nvl(dpt.a2013/ decode(dpt.p2013, 0, null, dpt.p2013), 0), 'FM90.00') as a5
from 
     (select code.code, 
          sum(decode(rp.REPORT_YEAR,'2009', decode(qrp.REPORT_YEAR, '2009', 1, 0 ), 0)) as p2009,
          sum(decode(rp.REPORT_YEAR,'2009', decode(qrp.REPORT_YEAR, '2009', nvl(rp.DPTDOS, 0) + nvl(qrp.YEAR_DPSTG_DOS, 0) , 0 ), 0)) as a2009,
          sum(decode(rp.REPORT_YEAR,'2010', decode(qrp.REPORT_YEAR, '2010', 1, 0 ), 0)) as p2010,
          sum(decode(rp.REPORT_YEAR,'2010', decode(qrp.REPORT_YEAR, '2010', nvl(rp.DPTDOS, 0) + nvl(qrp.YEAR_DPSTG_DOS, 0) , 0 ), 0)) as a2010,
          sum(decode(rp.REPORT_YEAR,'2011', decode(qrp.REPORT_YEAR, '2011', 1, 0 ), 0)) as p2011,
          sum(decode(rp.REPORT_YEAR,'2011', decode(qrp.REPORT_YEAR, '2011', nvl(rp.DPTDOS, 0) + nvl(qrp.YEAR_DPSTG_DOS, 0) , 0 ), 0)) as a2011,
          sum(decode(rp.REPORT_YEAR,'2012', decode(qrp.REPORT_YEAR, '2012', 1, 0 ), 0)) as p2012,
          sum(decode(rp.REPORT_YEAR,'2012', decode(qrp.REPORT_YEAR, '2012', nvl(rp.DPTDOS, 0) + nvl(qrp.YEAR_DPSTG_DOS, 0) , 0 ), 0)) as a2012,
          sum(decode(rp.REPORT_YEAR,'2013', decode(qrp.REPORT_YEAR, '2013', 1, 0 ), 0)) as p2013,
          sum(decode(rp.REPORT_YEAR,'2013', decode(qrp.REPORT_YEAR, '2013', nvl(rp.DPTDOS, 0) + nvl(qrp.YEAR_DPSTG_DOS, 0) , 0 ), 0)) as a2013
      from  (select code, CODE_NM from COMTCCMMNDETAILCODE where code_id = 'COM080') code
    left outer join org org on org.biz_cat = code.code 
    left outer join users users  on org.org_code = users.org_code
    left outer join qureport qrp on qrp.esntl_id = users.esntl_id 
    left outer join report rp  on rp.esntl_id = users.esntl_id 
    group by code.code order by code.code) dpt;

위와 같이 group by 해서 통계 쿼리를 작성했는데요. 시간이 32초 걸리네요.

select count(*) from qureport; >> 223191 

select count(*) from report; >> 6894682

select count(*) from users  >> 164468

select count(*) from org;  >> 3042

이정도의 데이터가 있는데요, 그럼 맨 위에 통계 쿼리 걸리는시간도 32 초가 적당한 속도 인건가요?

쿼리 튜닝 할수 있는 방법 있을까요? 속도 더 빠르게 나오게....

by 겸댕2후니 [2015.06.12 10:03:50]

플랜을 같이 올려주시겠어요? 그래야 분석이 가능할 것 같네요.

인덱스 정보도 부탁드려요

by 만년설 [2015.06.12 12:30:27]

select dpt.code,
        dpt.p2009 as p1, to_char(nvl(dpt.a2009/ decode(dpt.p2009, 0, null, dpt.p2009), 0), 'FM90.00') as a1,
        dpt.p2010 as p2, to_char(nvl(dpt.a2010/ decode(dpt.p2010, 0, null, dpt.p2010), 0), 'FM90.00') as a2,
        dpt.p2011 as p3, to_char(nvl(dpt.a2011/ decode(dpt.p2011, 0, null, dpt.p2011), 0), 'FM90.00') as a3,
        dpt.p2012 as p4, to_char(nvl(dpt.a2012/ decode(dpt.p2012, 0, null, dpt.p2012), 0), 'FM90.00') as a4,
        dpt.p2013 as p5, to_char(nvl(dpt.a2013/ decode(dpt.p2013, 0, null, dpt.p2013), 0), 'FM90.00') as a5
from 
     (select code.code, 
          sum(decode(report.REPORT_YEAR,'2009', 1 , 0)) as p2009,
          sum(decode(report.REPORT_YEAR,'2009', report.MSV , 0)) as a2009,
          sum(decode(report.REPORT_YEAR,'2010', 1 , 0)) as p2010,
          sum(decode(report.REPORT_YEAR,'2010', report.MSV , 0)) as a2010,
          sum(decode(report.REPORT_YEAR,'2011', 1 , 0)) as p2011,
          sum(decode(report.REPORT_YEAR,'2011', report.MSV , 0)) as a2011,
          sum(decode(report.REPORT_YEAR,'2012', 1 , 0)) as p2012,
          sum(decode(report.REPORT_YEAR,'2012', report.MSV , 0)) as a2012,
          sum(decode(report.REPORT_YEAR,'2013', 1 , 0)) as p2013,
          sum(decode(report.REPORT_YEAR,'2013', report.MSV , 0)) as a2013
      from  (select code, CODE_NM from COMTCCMMNDETAILCODE where code_id = 'COM080') code
    left outer join org org on org.biz_cat = code.code 
    left outer join users users  on org.org_code = users.org_code
    left outer join(SELECT ESNTL_ID, REPORT_YEAR, SUM(MSV) MSV FROM (
          SELECT ESNTL_ID, REPORT_YEAR, QU, DPTDOS MSV FROM REPORT
          WHERE REPORT_YEAR  BETWEEN  '2009' and '2013'
            UNION ALL
          SELECT ESNTL_ID, REPORT_YEAR, QU, YEAR_DPSTG_DOS MSV FROM QUREPORT
          WHERE REPORT_YEAR BETWEEN  '2009' and '2013'
      ) GROUP BY ESNTL_ID, REPORT_YEAR) report on report.ESNTL_ID = users.ESNTL_ID
    group by rollup(code.code) order by code.code) dpt ;

자 답 이요 회사 동료분 짠 쿼리 참고 해서 하니까 1.5 초 걸리네요 .

report 가 600 만건이라는걸... 생각 않고 바로 조인 걸어버린걸 고쳣어요

report 를 조인 걸기 전에 년도 조건으로 한번 걸러서 가져와서 조인 거니까 빨리지네요~ ㅎㅎ

by swlee710 [2015.06.12 16:20:22]

sum을 하실 거라면 decode에 0 부분도 지우셔도 되겠어요..

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변