对一张日/月运营报表的性能优化(使用 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
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
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