|
집합의 최적화
튜닝전
select sysdate, win.sale_draw_no, sysdate, sysdate,
to_char(nvl((SUM(win.win_game_1)), 0) ,'999,999,999'),
to_char(nvl(SUM(win.win_amount_1), 0),'999,999,999,999,999,999'),
to_char(nvl((SUM(win.win_game_2)), 0) ,'999,999,999'),
to_char(nvl(SUM(win.win_amount_2), 0),'999,999,999,999,999,999'),
to_char(nvl((SUM(win.win_game_3)), 0) ,'999,999,999'),
to_char(nvl(SUM(win.win_amount_3), 0),'999,999,999,999,999,999'),
to_char(nvl((SUM(win.win_game_4)), 0) ,'999,999,999'),
to_char(nvl(SUM(win.win_amount_4), 0),'999,999,999,999,999,999'),
to_char(nvl((SUM(win.win_game_5)), 0) ,'999,999,999'),
to_char(nvl(SUM(win.win_amount_5), 0),'999,999,999,999,999,999'),
to_char(nvl((SUM(win.pay_tickets)), 0) ,'999,999,999'), nvl(ld.draw_date, 0),
nvl(ld.pay_exp_date,0)
from win_pay_info win, lotto_draw ld
where 170<= win.sale_draw_no
and win.sale_draw_no <= 171
and win.sale_draw_no = ld.draw_no
and win.retailer_id like '11100003%'
group by sale_draw_no, ld.draw_date, ld.pay_exp_date
call count cpu elapsed disk query current rows
-------------------------------------------------------------------
Parse 1 0.00 0.04 1 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 37.06 88.76 111492 115225 4 2
-------------------------------------------------------------------
total 4 37.06 88.80 111493 115225 5 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21
Rows Row Source Operation
-------------------------------------------------------------------
2 SORT GROUP BY
27 NESTED LOOPS
175 TABLE ACCESS FULL LOTTO_DRAW
27 TABLE ACCESS BY INDEX ROWID WIN_PAY_INFO
271565 INDEX RANGE SCAN (object id 4025)
-------------------------------------------------------------------
설명.
정말 간단합니다. Plan을 보시면 아시겠지만 튜닝전 SQL은 win_pay_info와 lotto_draw 테이블과 모두 조인을 한 이후 gorup by을 합니다. 즉 모든 건을 NL조인을 합니다. 튜닝후 SQL은 일단 win_play_info을 우리가 원하는 집합으로 만든 이후 lotto_draw와 조인을 합니다. 비슷해 보이지만 결과는 정말 천지차이죠.. ^^
튜닝후
select sysdate, win.sale_draw_no, sysdate, sysdate,
win_game_1, win_amount_1, win_game_2, win_amount_2,win_game_3,
win_amount_3, win_game_4, win_amount_4,win_game_5,
win_amount_5,pay_tickets, nvl(ld.draw_date,0), nvl(ld.pay_exp_date,0)
from (select sale_draw_no,
to_char(nvl((SUM(win.win_game_1)), 0) ,'999,999,999') win_game_1,
to_char(nvl(SUM(win.win_amount_1), 0),'999,999,999,999,999,999') win_amount_1,
to_char(nvl((SUM(win.win_game_2)), 0) ,'999,999,999') win_game_2,
to_char(nvl(SUM(win.win_amount_2), 0),'999,999,999,999,999,999') win_amount_2,
to_char(nvl((SUM(win.win_game_3)), 0) ,'999,999,999') win_game_3,
to_char(nvl(SUM(win.win_amount_3), 0),'999,999,999,999,999,999') win_amount_3,
to_char(nvl((SUM(win.win_game_4)), 0) ,'999,999,999') win_game_4,
to_char(nvl(SUM(win.win_amount_4), 0),'999,999,999,999,999,999') win_amount_4,
to_char(nvl((SUM(win.win_game_5)), 0) ,'999,999,999') win_game_5,
to_char(nvl(SUM(win.win_amount_5), 0),'999,999,999,999,999,999') win_amount_5,
to_char(nvl((SUM(win.pay_tickets)), 0) ,'999,999,999') pay_tickets
from win_pay_info win
where 170<= win.sale_draw_no
and win.sale_draw_no <= 171
and win.retailer_id like '11100003%'
group by sale_draw_no ) win,
lotto_draw ld
where win.sale_draw_no = ld.draw_no
call count cpu elapsed disk query current rows
-------------------------------------------------------------------Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.86 0.85 0 4632 4 2
-------------------------------------------------------------------
total 4 0.86 0.86 0 4632 4 2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21
Rows Row Source Operation
-------------------------------------------------------------------
2 MERGE JOIN
175 SORT JOIN
174 TABLE ACCESS FULL LOTTO_DRAW
2 SORT JOIN
2 VIEW
2 SORT GROUP BY
27 TABLE ACCESS BY INDEX ROWID WIN_PAY_INFO
271392 INDEX RANGE SCAN (object id 4025)
-------------------------------------------------------------------
결과 : 88.80 초 -> 0.86 초
참고
위와 같이 win_play_info의 데이터가 많은 경우에는 group by한 이후 조인을 하는 것이 좋지만 데이터가 많지 않은 경우엔 별다른 효과가 없습니다. |
