집합의 최적화 (2)

집합의 최적화2
        
튜닝전
select p.problem_code_id, nvl(temp.count,0)
from   problem_code p,
         (select h.problem_code_id, count(h.problem_code_id) count
          from    hot_call h, web_user w
          where to_date('2004-01-01-000000', 'YYYY-MM-DD-HH24MISS') < h.accept_date

          and    h.accept_date < to_date('2006-03-28-235959','YYYY-MM-DD-HH24MISS')
          and    h.del_flag=1 and h.terminal_type like '%'
          and    w.user_id=h.user_id and (w.sys_dept_id=6 or h.retailer_number like '3%')
          group by problem_code_id) temp
where p.problem_code_id = temp.problem_code_id(+) and p.problem_code_id like 'CO%'

call       count     cpu     elapsed     disk    query    current    rows
---------------------------------------------------------------
Parse       1        0.00       0.00          0          0          0            0
Execute    1        0.00       0.00          0          0          0            0
Fetch       2        3.52        3.57       4416     473792     0             5
---------------------------------------------------------------
total         4        3.52        3.57       4416     473792     0             5

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 401

Rows     Row Source Operation
-----------------------------------------------------
   5         MERGE JOIN OUTER
   6         INDEX RANGE SCAN (object id 249175)
   5         SORT JOIN
 102        VIEW
 102        SORT GROUP BY
 55837     NESTED LOOPS
230956    TABLE ACCESS BY INDEX ROWID HOT_CALL
232139    INDEX RANGE SCAN (object id 249167)
 55837    TABLE ACCESS BY INDEX ROWID WEB_USER
461910    INDEX UNIQUE SCAN (object id 249190)
-----------------------------------------------------

설명.

인라인뷰 temp안에서 집합을 만들어 내고 그 데이터와 problem_code 테이블과 머지 아웃터 조인을 합니다. p.problem_code_id = temp.problem_code_id(+) 조건이 있는데.. 즉.. p에 의해 만들어지 집합은 반드시 나와야 된다는 의미인데, p.problem_code_id like 'CO%' 조건이 있습니다. 그럼, 어차피 p.problem_code_id = temp.problem_code_id(+) 조건이 있고 이왕이면 temp 집합을 만들때 problem_code_id like 'CO%'의 조건을 temp안에 넣으면 더 최적화되 temp집합을 만들테고 그것과 조인을 하면 전체적으로 많은 성능향상을 이루게 됩니다.

튜닝후

select p.problem_code_id, nvl(temp.count,0)
from   problem_code p,
         (select h.problem_code_id, count(h.problem_code_id) count
          from    hot_call h, web_user w
          where to_date('2004-01-01-000000', 'YYYY-MM-DD-HH24MISS') < h.accept_date

          and    h.accept_date < to_date('2006-03-28-235959','YYYY-MM-DD-HH24MISS')
          and    h.del_flag = 1

          and    h.terminal_type like '%'
          and    w.user_id = h.user_id

          and    (w.sys_dept_id = 6 or h.retailer_number like '3%')
          and    h.problem_code_id like 'CO%'
          group by problem_code_id) temp
where p.problem_code_id = temp.problem_code_id(+) and p.problem_code_id like 'CO%'

call       count     cpu     elapsed     disk    query    current    rows
---------------------------------------------------------------
Parse       1        0.00       0.00          0          0          0           0
Execute    1        0.00       0.00          0          0          0           0
Fetch        2        1.30       1.48       4415      55610      0           5
---------------------------------------------------------------
total          4        1.30       1.48       4415      55610      0           5

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

Rows     Row Source Operation
----------------------------------------------------
    5       MERGE JOIN OUTER
    6       INDEX RANGE SCAN (object id 249175)
    5       SORT JOIN
    5       VIEW
    5       SORT GROUP BY
 2913     NESTED LOOPS
 21865   TABLE ACCESS BY INDEX ROWID HOT_CALL
232139   INDEX RANGE SCAN (object id 249167)
 2913     TABLE ACCESS BY INDEX ROWID WEB_USER
 43728    INDEX UNIQUE SCAN (object id 249190)
----------------------------------------------------
결과 : 3.57초 -> 1.48초