튜닝예제(3)

결합인덱스 순서의 중요성

process_retailer_input : 약 800만건

- 인덱스

win_sale_info_draw_no_id : period_flag+draw_no+retailer_id
win_sale_info_sale_date  : period_flag+sale_date_retailer_id
win_sale_info_state_name : state_name+draw_no+sale_date
        
- 튜닝전
SELECT sale_date,

             sysdate, sysdate,

             sysdate, to_char(nvl((SUM(win_game_1)), 0),'999,999,999'),

             to_char(nvl(SUM(win_amount_1), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_2)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_2), 0),'999,999,999,999,999,999'),
             to_char(nvl((SUM(win_game_3)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_3), 0),'999,999,999,999,999,999'),
             to_char(nvl((SUM(win_game_4)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_4), 0),'999,999,999,999,999,999'),
             to_char(nvl((SUM(win_game_5)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_5), 0),'999,999,999,999,999,999')
FROM    win_sale_info
WHERE 170<= draw_no

AND     draw_no <= 171

AND     retailer_id like '11100003%'

Group by sale_date

call       count       cpu      elapsed       disk      query    current     rows
--------------------------------------------------------------------
Parse        1         0.00        0.03            0            0           0            0
Execute     1         0.00        0.00            0            0           0            0
Fetch         2       16.94       17.48         86002      87794       36          13
--------------------------------------------------------------------
total           4       16.94       17.51         86002      87794       36          13

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21

Rows     Row Source Operation
--------------------------------------------------------------------
  13         SORT GROUP BY
  13         TABLE ACCESS FULL WIN_SALE_INFO

--------------------------------------------------------------------

설명:

결합인덱스 순서 선정의 가장 우선시 되는 것은 컬럼의 분포도가 아니라 항상 조건이 들어오는 것입니다. 위 SQL 조건에 들어오는 컬럼 draw_id와 retailer_id가 쓸 수 있을 것 같은 인덱스는 win_sale_info_draw_no_id 입니다. 하지만 첫번째 컬럼인 period_flag가 없어서 win_sale_info_draw_no_id인덱스를 사용하지 못하고 full 스캔을 하고 있습니다. 제가 확인해 본 결과 period_flag의 값은 ('D','R','W') 뿐입니다. 그래서 이 조건을 입력하면 win_sale_info_draw_no_id 인덱스를 사용할 수 있습니다.

- 튜닝후
SELECT sale_date,

             sysdate,

             sysdate,

             sysdate,

             to_char(nvl((SUM(win_game_1)), 0),'999,999,999'),

             to_char(nvl(SUM(win_amount_1), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_2)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_2), 0),'999,999,999,999,999,999'),
             to_char(nvl((SUM(win_game_3)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_3), 0),'999,999,999,999,999,999'),
             to_char(nvl((SUM(win_game_4)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_4), 0),'999,999,999,999,999,999'),
             to_char(nvl((SUM(win_game_5)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_5), 0),'999,999,999,999,999,999')
FROM    win_sale_info
WHERE 170<= draw_no

AND     draw_no <= 171

AND     retailer_id like '11100003%'

and      period_flag in ('D','R','W')

Group by sale_date

call       count       cpu      elapsed       disk      query    current     rows
--------------------------------------------------------------------
Parse        1        0.00         0.01            0          0            0             0
Execute     1        0.00         0.00            0          0            0             0
Fetch         2       0.09         0.09            0        350           0            13

--------------------------------------------------------------------
total           4       0.09         0.10            0        350          0             13

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21

Rows     Row Source Operation

--------------------------------------------------------------------
  13         SORT GROUP BY
  13         CONCATENATION
   0         TABLE ACCESS BY INDEX ROWID WIN_SALE_INFO
   1          INDEX RANGE SCAN (object id 3991)
  13         TABLE ACCESS BY INDEX ROWID WIN_SALE_INFO
  14         INDEX RANGE SCAN (object id 3991)
   0         TABLE ACCESS BY INDEX ROWID WIN_SALE_INFO
   1         INDEX RANGE SCAN (object id 3991)

--------------------------------------------------------------------
결과 : 17.51초 -> 0.10초

참고.

오라클의 코아가 향상되면서 이렇게 결함인덱스의 첫컬럼이 생략되어도 두번째 컬럼부터 찾아갈 수 있는 옵티마이져 로직이 추가되긴 했지만, 현재 운영장비 오라클 버젼에서는 이런 처리 로직를 할 수 없습니다. 할 수 있다고 하더라도 이렇게 명시적으로 작성해 주는 것이 더 명확하겠죠.