Query tuning
Below is the query and its plan
select a.src_id shortcode,a.pre_res_type prt,nvl(a.mtcount,0) txtcnt,
nvl(a.mtrevenue/100,0) txtrev,nvl(b.mtcount,0) nontxtcnt,nvl(b.mtrevenue/100,0) nontxtrev,
nvl(c.mtcount,0) subcnt,nvl(c.mtrevenue/100,0) subrev
from (select src_id,pre_res_type,count(1) mtcount,sum(pur_price_exvat) mtrevenue from view_sms_mt where pre_res_type
not in ('RT%','PIC%','SUB%') and todate between '01-Apr-2011' and '30-Apr-2011' group by src_id,pre_res_type) a,
(select src_id,pre_res_type,count(1) mtcount,sum(pur_price_exvat) mtrevenue from view_sms_mt where pre_res_type in ('RT%','PIC%') and
select a.src_id shortcode,a.pre_res_type prt,nvl(a.mtcount,0) txtcnt,
nvl(a.mtrevenue/100,0) txtrev,nvl(b.mtcount,0) nontxtcnt,nvl(b.mtrevenue/100,0) nontxtrev,
nvl(c.mtcount,0) subcnt,nvl(c.mtrevenue/100,0) subrev
from (select src_id,pre_res_type,count(1) mtcount,sum(pur_price_exvat) mtrevenue from view_sms_mt where pre_res_type
not in ('RT%','PIC%','SUB%') and todate between '01-Apr-2011' and '30-Apr-2011' group by src_id,pre_res_type) a,
(select src_id,pre_res_type,count(1) mtcount,sum(pur_price_exvat) mtrevenue from view_sms_mt where pre_res_type in ('RT%','PIC%') and
0