对一张日/月运营报表的性能优化(使用 EXISTS和DECODE)

 

对一张日/月运营报表的性能优化.

说明,table1是百万级数据量的表.对查询优化后,性能提高了50%以上,报表显示时间从2分钟缩短到1分钟这内.

优化点:在WHERE语句中使用EXISTS 代替IN

    使用decode函数减少大表连接的次数,减少响应时间,decode函数支持分组.

       下面贴出优化前后游标内的部分查询语句,有想法者,请讨论........
    

before
  1select  d.deptname,
  2 .
  3 .
  4 nvl(ic_return.ic_return,0) ic_return,
  5 nvl(ic_capture.ic_capture,0) ic_capture,
  6 nvl(single_ticket.single_ticket,0) single_ticket,
  7 nvl(ic_refund.ic_refund,0) ic_refund,
  8 nvl(ic_refund.remain_money,0)  remain_money,
  9 nvl(remain_money.ic_fill,0) ic_fill,
 10 nvl(money_refresh.money_refresh,0) money_refresh 
 11from 
 12   ..
 13   ..     
 14    (   
 15      select s.id ,sum(nvl(to_number(t.deal_num2),0)*nvl(t.deal_fee,0)) ic_capture  from table1 t ,table2 s 
 16      where t.line_id=s.line_id and t.station_id=s.station_id  and
 17      t.tr_type_id='51' and
 18      t.paymode_id='02' and
 19      s.line_id= &i_line_id and
 20      t.operator_id in(select u.id from users u where u.id) and
 21      to_char(to_date(t.squad_day,'yyyymmdd'),decode(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm'))=to_char(to_date(&d_reportdate,'yyyymmdd'),decode                 
 22
 23 (&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm')) 
 24      
 25      group by s.id
 26
 27    ) ic_capture,
 28     (   
 29      select s.id ,sum(nvl(to_number(t.deal_num2),0)*nvl(t.deal_fee,0)) ic_fill  from table1 t ,table2 s 
 30      where t.line_id=s.line_id and t.station_id=s.station_id  and
 31      t.tr_type_id='54' and
 32      t.paymode_id='14' and
 33      s.line_id= &i_line_id and
 34       
 35      to_char(to_date(t.squad_day,'yyyymmdd'),decode(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm'))=to_char(to_date(&d_reportdate,'yyyymmdd'),decode               
 36
 37(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm'))  and
 38      t.operator_id in(select u.id from users u where u.id) 
 39      group by s.id
 40
 41    ) ic_fill,
 42     (   
 43      select s.id ,sum(nvl(to_number(t.deal_num2),0)*nvl(t.deal_fee,0)) ic_return  from table1 t ,table2 s 
 44      where t.line_id=s.line_id and t.station_id=s.station_id  and
 45      t.tr_type_id='57' and
 46      t.paymode_id='02' and
 47      s.line_id= &i_line_id and
 48      to_char(to_date(t.squad_day,'yyyymmdd'),decode(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm'))=to_char(to_date(&d_reportdate,'yyyymmdd'),decode            
 49
 50(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm')) and
 51        t.operator_id in(select u.id from users u where u.id) 
 52      group by s.id
 53
 54    ) ic_return,
 55     (   
 56      select s.id ,sum(nvl(to_number(t.deal_num2),0)*nvl(t.deal_fee,0)) money_refresh  from table1 t ,table2 s 
 57      where t.line_id=s.line_id and t.station_id=s.station_id  and
 58      t.tr_type_id='56' and
 59      t.paymode_id='01' and
 60       t.operator_id in(select u.id from users u where u.id) and
 61      s.line_id= &i_line_id and
 62      to_char(to_date(t.squad_day,'yyyymmdd'),decode(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm'))=to_char(to_date(&d_reportdate,'yyyymmdd'),decode                 
 63
 64    (&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm')) 
 65      
 66      group by s.id
 67
 68    ) money_refresh,
 69     (   
 70      select s.id ,sum(nvl(to_number(t.deal_num2),0)*nvl(t.deal_fee,0)) single_ticket  from table1 t ,table2 s 
 71      where t.line_id=s.line_id and t.station_id=s.station_id  and
 72      t.tr_type_id='50' and
 73       t.operator_id in(select u.id from users u where u.id) and
 74      s.line_id= &i_line_id and
 75      to_char(to_date(t.squad_day,'yyyymmdd'),decode(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm'))=to_char(to_date(&d_reportdate,'yyyymmdd'),decode                 
 76
 77 (&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm')) 
 78      
 79      group by s.id
 80
 81     ) single_ticket,
 82      (   
 83      select s.id ,sum(nvl(to_number(t.deal_num2),0)*nvl(t.return_bala,0)) remain_money  from table1 t ,table2 s 
 84      where t.line_id=s.line_id and t.station_id=s.station_id  and
 85    
 86      t.operator_id in(select u.id from users u where u.id) and
 87      s.line_id= &i_line_id and
 88      to_char(to_date(t.squad_day,'yyyymmdd'),decode(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm'))=to_char(to_date(&d_reportdate,'yyyymmdd'),decode                
 89
 90(&i_dayOrMonth,0,'yyyymmdd',1,'yyyymm')) 
 91      
 92      group by s.id
 93
 94     ) remain_money,   
 95    
 96    dept d --车站  
 97   
 98    where    
 99    ..
100    ic_return.id(+)=d.id and
101    single_ticket.id(+)=d.id and
102    ic_refund .id(+)=d.id and
103    ic_fill.id(+)=d.id and 
104    remain_money.id(+)=d.id and
105    money_refresh.id(+)=d.id and
106    ic_capture.id(+)=d.id 
107     
108    
109  
110 
111   

last
 1select  d.deptname,
 2 .
 3 .
 4 nvl(qfuser.ic_return,0) ic_return,
 5 nvl(qfuser.ic_capture,0) ic_capture,
 6 nvl(qfuser.single_ticket,0) single_ticket,
 7 nvl(qfuser.ic_refund,0) ic_refund,
 8 nvl(qfuser.remain_money,0)  remain_money,
 9 nvl(qfuser.ic_fill,0) ic_fill,
10 nvl(qfuser.money_refresh,0) money_refresh 
11from 
12   ..
13   ..     
14( select s.id ,
15     
16    sum(decode(t.tr_type_id,'05',to_number(nvl(t.deal_num2,0))*nvl(t.deal_fee,0)))
17
18    single_ticket  ,
19    sum(decode(t.tr_type_id||t.paymode_id,'5702',to_number(nvl(t.deal_num2,0))*nvl
20
21    (t.deal_fee,0))) ic_return  ,
22    sum(decode(t.tr_type_id||t.paymode_id,'5601',to_number(nvl(t.deal_num2,0))*nvl
23
24    (t.deal_fee,0)))  money_refresh ,
25     
26    sum(decode(t.tr_type_id||t.paymode_id,'5414',to_number(nvl(t.deal_num2,0))*nvl
27
28    (t.deal_fee,0)))  ic_fill,
29    sum(decode(t.tr_type_id||t.paymode_id,'5102',to_number(nvl(t.deal_num2,0))*nvl
30
31    (t.deal_fee,0)))  ic_capture,
32    sum(to_number(nvl(t.deal_num2,0))*nvl(t.return_bala,0))   remain_money      
33 from table1 t ,table2 s 
34    where t.line_id(+)=s.line_id and t.station_id(+)=s.station_id  and      
35      s.line_id=i_line_id and
36      to_char(to_date(t.squad_day,'yyyymmdd'),decode
37
38     (i_dayOrMonth,0,'yyyymmdd',1,'yyyymm'))=to_char(d_reportdate,decode
39
40     (i_dayOrMonth,0,'yyyymmdd',1,'yyyymm')) and
41     EXISTS (select u.id from users u where u.id=t.operator_id) 
42 group by s.id      
43      
44      ) qfuser,  
45    
46    dept d --车站  
47   
48    where    
49    ..
50    qfuser.id(+)=d.id and