안녕하세요.

최근 업무에서 필요한 쿼리를 작성중에, 결과값은 제대로 얻었지만 쿼리 실행 속도가 문제가 되서

여러가지로 수정작업을 하던중 한계를 느껴서 질문을 하게 되었습니다.

내용은 1년치의 골프장 매출현황을 필요에 맞게 가져오는것이구요.


================================================================================

  select
   substr(a.sale_date, 1, 6) as sale_date,
   NVL((SELECT COUNT(distinct(BOOK_DATE))
    FROM GBA01M WHERE CORP_CODE = '02' AND WORKGRP_CODE = '100' AND SUBSTR(BOOK_DATE, 1, 6) = SUBSTR(A.SALE_DATE, 1, 6) GROUP BY SUBSTR(BOOK_DATE, 1, 6)),0) AS GOLFDAY_CNT,
   nvl(max(b.member_count),0) as member_count,
  (select
   to_char(nvl(sum(case hole_code
        when '001' then 0.5
        else 1
       end), 0), '999,990.0')
   from gba01m gm
   where corp_code = a.corp_code
   and workgrp_code = '100'
   and 1 = (select item1
     from coz10s
     where gm.corp_code = corp_code
     and head = 'RS511'
     and detail = gm.cose
     and use_yn = 'Y')
   and book_date like SUBSTR(A.SALE_DATE, 1, 6) || '%') as member_team,
   nvl(max(b.member_front),0) as member_front,
   NVL(b.member_lent,0) + NVL(e.cart, 0) - (round(NVL(b.member_lent, 0) / 1.1*0.1) + round(NVL(e.cart, 0) / 1.1*0.1)) as member_lent,
   nvl(sum(decode(a.upjang_code, '1102', net_amt)), 0) as member_club_house,
   nvl(sum(decode(a.upjang_code, '1106', net_amt)), 0) as member_st_club_house,
   nvl(sum(decode(a.upjang_code, '1103', net_amt)), 0) as member_west_th,
   nvl(sum(decode(a.upjang_code, '1104', net_amt)), 0) as member_center_th,
   nvl(max(c.guest_count),0) as guest_count,
   (select
   to_char(nvl(sum(case hole_code
        when '001' then 0.5
        else 1
       end), 0), '999,990.0')
   from gba01m gm
   where corp_code = a.corp_code
   and workgrp_code = '100'
   and 2 = (select item1
     from coz10s
     where gm.corp_code = corp_code
     and head = 'RS511'
     and detail = gm.cose
     and use_yn = 'Y')
   and book_date like SUBSTR(A.SALE_DATE, 1, 6) || '%') as guest_team,
   nvl(max(c.guest_front),0) as guest_front,
   NVL(c.guest_lent,0) + NVL(e.n_cart, 0) - (round(NVL(c.guest_lent, 0) / 1.1*0.1) + round(NVL(e.n_cart, 0) / 1.1*0.1)) as guest_lent,
   nvl(sum(decode(a.upjang_code, '1105', net_amt)), 0) as guest_east_th,
   nvl(sum(decode(a.upjang_code, '1191', net_amt)), 0) as pro_shop,
   nvl(sum(decode(a.upjang_code, '1192', net_amt)), 0) as emp_res,
   nvl(sum(decode(a.upjang_code, '1199', net_amt)), 0) as etc_upjang,
   nvl(sum(decode(substr(a.upjang_code, 1, 1), '1', net_amt)), 0) as golf_sum,
   nvl(sum(net_amt), 0) as resort_sum,
   NVL(D.LAST_GOLFSUM,0) AS LAST_GOLFSUM,
   NVL(D.LAST_RESORTAMT,0) AS LAST_RESORTAMT,
   DECODE(NVL(D.LAST_GOLFSUM,0),0,0,ROUND(NVL(SUM(DECODE(SUBSTR(A.UPJANG_CODE,1,1), '1', NET_AMT)),0)*100/NVL(D.LAST_GOLFSUM,0)-100,0)) AS GOLF_COMPARE,
   DECODE(NVL(D.LAST_RESORTAMT,0),0,0,ROUND(NVL(SUM(NET_AMT),0)*100/NVL(D.LAST_RESORTAMT,0)-100,0)) AS RESORT_COMPARE
 from baa07s a,
  (select
   SUBSTR(gd.BOOK_DATE, 1, 6) as book_date,
   to_char(nvl(sum(case gm.hole_code
        when '001' then 0.5
        else 1
       end), 0), '999,990.0') as member_count,
    sum(gd.ENTR_FEE) as member_front,
    sum(gd.LENT_FEE) as member_lent
   from gba01d gd, gba01m gm
   where gd.corp_code = gm.corp_code
   and gd.workgrp_code = gm.workgrp_code
   and gd.book_date = gm.book_date
   and gd.cose   = gm.cose
   and 1 = (select item1
     from coz10s
     where gd.corp_code = corp_code
     and head = 'RS511'
     and detail = gd.cose
     and use_yn = 'Y')
   and gd.time   = gm.time
   and gd.corp_code = '02'
   and gd.workgrp_code = '100'
   and gd.del_yn  = 'N'
   and gd.BOOK_DATE LIKE '2012' || '%'
   group by SUBSTR(gd.BOOK_DATE, 1, 6)) b,
  (select
   SUBSTR(gd.BOOK_DATE, 1, 6) as book_date,
   to_char(nvl(sum(case gm.hole_code
        when '001' then 0.5
        else 1
       end), 0), '999,990.0') as guest_count,
   sum(gd.ENTR_FEE) as guest_front,
   sum(gd.LENT_FEE) as guest_lent
   from gba01d gd, gba01m gm
   where gd.corp_code = gm.corp_code
   and gd.workgrp_code = gm.workgrp_code
   and gd.book_date = gm.book_date
   and gd.cose   = gm.cose
   and 2 = (select item1
     from coz10s
     where gd.corp_code = corp_code
     and head = 'RS511'
     and detail = gd.cose
     and use_yn = 'Y')
   and gd.time   = gm.time
   and gd.corp_code = '02'
   and gd.workgrp_code = '100'
   and gd.del_yn  = 'N'
   and gd.BOOK_DATE LIKE '2012' || '%'
   group by SUBSTR(gd.BOOK_DATE, 1, 6)) c,
   (SELECT
    MAX(TO_CHAR(ADD_MONTHS(TO_DATE(SALE_DATE, 'YYYYMMDD'), 12), 'YYYYMM')) AS SALE_MON,
    NVL(SUM(DECODE(SUBSTR(UPJANG_CODE,1,1), '1', NET_AMT)),0) AS LAST_GOLFSUM,
    NVL(SUM(NET_AMT),0) AS LAST_RESORTAMT
   FROM BAA07S
   WHERE CORP_CODE = '02'
    AND SALE_DATE LIKE '2012' - 1|| '%'
    GROUP BY SUBSTR(SALE_DATE, 1, 6)
   ) d,
   (select SUBSTR(BOOK_DATE, 1, 6) as book_date,
     sum(decode(cose, '007', 0, CART_FEE)) as cart,
     sum(decode(cose, '007', CART_FEE, 0)) as n_cart
   from gbc03s
   where corp_code = '02'
   and WORKGRP_CODE = '100'
   and BOOK_DATE LIKE '2012' || '%'
   group by SUBSTR(BOOK_DATE, 1, 6)) e
 where a.corp_code = '02'
 and SUBSTR(a.sale_date, 1, 6) = b.book_date(+)
 and SUBSTR(a.sale_date, 1, 6) = c.book_date(+)
 and SUBSTR(A.SALE_DATE, 1, 6) = d.SALE_MON(+)
 and SUBSTR(a.sale_date, 1, 6) = e.book_date(+)
 and a.sale_date LIKE '2012' || '%'
 group by a.corp_code, SUBSTR(a.SALE_DATE, 1, 6), b.member_lent, c.guest_lent, e.cart, e.n_cart, d.LAST_GOLFSUM, d.LAST_RESORTAMT
 order by SUBSTR(A.SALE_DATE, 1, 6)


===============================================================================
쿼리문은 위와 같습니다.

오라클을 다룬지 얼마안된 초보라서 어설픈 쿼리문이지만,
검색 속도를 올릴 수 있는 팁이나, 그외 조언을 주시면 고맙겠습니다.