select caad.approve_one_no as tb1,
caad.approve_one_name as tb2,
NVL(sum(caad.apply_rmb_amt),0) as tb3,
NVL(sum(case caad.approve_conclusion_cd when '02' then caad.approve_rmb_amt end),0) + NVL(sum(case caad.approve_conclusion_cd when '03' then caad.approve_rmb_amt end),0) as tb4,
decode(NVL(sum(caad.apply_rmb_amt),0),0,0,(NVL(sum(case caad.approve_conclusion_cd when '02' then caad.approve_rmb_amt end),0) + NVL(sum(case caad.approve_conclusion_cd when '03' then caad.approve_rmb_amt end),0)) / NVL(sum(caad.apply_rmb_amt),0)) as tb5,
NVL(sum(case when caad.customer_credit_policy_cd = '1' and caad.approve_conclusion_cd in ('02','03') then caad.approve_rmb_amt end),0) as tb6,
NVL(sum(case when caad.customer_r3_cd in ('101','102','113','201','202','301','302','0504','0503','0502','0501','2101','2102','2103') and caad.approve_conclusion_cd in ('02','03') then decode(caad.customer_credit_policy_cd,'1',null,caad.approve_rmb_amt) end),0) + NVL(sum(case when caad.customer_credit_policy_cd = '1' and caad.approve_conclusion_cd in ('02','03') then caad.approve_rmb_amt end),0) as tb7,
NVL(sum(case when caad.customer_r3_cd in ('103','114','203','303','0505','0506','2104','2105') and caad.approve_conclusion_cd in ('02','03') then decode(caad.customer_credit_policy_cd,'1',null,caad.approve_rmb_amt) end),0) as tb8,
NVL(sum(case when caad.customer_r3_cd in ('104','105','106','107','108','109','110','115','116','117','204','205','206','207','304','305','306','307','308','309','310','420','0507','0508','0509','0510','0511','0512','0513','0514','0515','0516','2106','2107','2108','2109','2110') and caad.approve_conclusion_cd in ('02','03') then decode(caad.customer_credit_policy_cd,'1',null,caad.approve_rmb_amt) end),0) as tb9,
NVL(sum(case when caad.customer_r3_cd in ('111','112') and caad.approve_conclusion_cd in ('02','03') then decode(caad.customer_credit_policy_cd,'1',null,caad.approve_rmb_amt) end),0) as tb10
from crms_ac_approve_detail caad
where (caad.industry_one_cd in ('73','90','88','11','10','09','08','06','84','85','39','36','01','02','03','04','05','13','74','81','51','52','53','54','55','56','57','44','07','60','45','46','58','59','79','80') or caad.industry_two_cd in ('371') or caad.industry_trd_cd in ('8920','8910'))
and caad.declare_type_cd = '2'
and caad.biz_nature_cd not in ('16','18')
and caad.report_date = to_date('20090831','yyyymmdd')
and caad.reply_date >= to_date('20090101','yyyymmdd')
and caad.reply_date <= to_date('20090831','yyyymmdd')
group by caad.approve_one_no,caad.approve_one_name
caad.approve_one_name as tb2,
NVL(sum(caad.apply_rmb_amt),0) as tb3,
NVL(sum(case caad.approve_conclusion_cd when '02' then caad.approve_rmb_amt end),0) + NVL(sum(case caad.approve_conclusion_cd when '03' then caad.approve_rmb_amt end),0) as tb4,
decode(NVL(sum(caad.apply_rmb_amt),0),0,0,(NVL(sum(case caad.approve_conclusion_cd when '02' then caad.approve_rmb_amt end),0) + NVL(sum(case caad.approve_conclusion_cd when '03' then caad.approve_rmb_amt end),0)) / NVL(sum(caad.apply_rmb_amt),0)) as tb5,
NVL(sum(case when caad.customer_credit_policy_cd = '1' and caad.approve_conclusion_cd in ('02','03') then caad.approve_rmb_amt end),0) as tb6,
NVL(sum(case when caad.customer_r3_cd in ('101','102','113','201','202','301','302','0504','0503','0502','0501','2101','2102','2103') and caad.approve_conclusion_cd in ('02','03') then decode(caad.customer_credit_policy_cd,'1',null,caad.approve_rmb_amt) end),0) + NVL(sum(case when caad.customer_credit_policy_cd = '1' and caad.approve_conclusion_cd in ('02','03') then caad.approve_rmb_amt end),0) as tb7,
NVL(sum(case when caad.customer_r3_cd in ('103','114','203','303','0505','0506','2104','2105') and caad.approve_conclusion_cd in ('02','03') then decode(caad.customer_credit_policy_cd,'1',null,caad.approve_rmb_amt) end),0) as tb8,
NVL(sum(case when caad.customer_r3_cd in ('104','105','106','107','108','109','110','115','116','117','204','205','206','207','304','305','306','307','308','309','310','420','0507','0508','0509','0510','0511','0512','0513','0514','0515','0516','2106','2107','2108','2109','2110') and caad.approve_conclusion_cd in ('02','03') then decode(caad.customer_credit_policy_cd,'1',null,caad.approve_rmb_amt) end),0) as tb9,
NVL(sum(case when caad.customer_r3_cd in ('111','112') and caad.approve_conclusion_cd in ('02','03') then decode(caad.customer_credit_policy_cd,'1',null,caad.approve_rmb_amt) end),0) as tb10
from crms_ac_approve_detail caad
where (caad.industry_one_cd in ('73','90','88','11','10','09','08','06','84','85','39','36','01','02','03','04','05','13','74','81','51','52','53','54','55','56','57','44','07','60','45','46','58','59','79','80') or caad.industry_two_cd in ('371') or caad.industry_trd_cd in ('8920','8910'))
and caad.declare_type_cd = '2'
and caad.biz_nature_cd not in ('16','18')
and caad.report_date = to_date('20090831','yyyymmdd')
and caad.reply_date >= to_date('20090101','yyyymmdd')
and caad.reply_date <= to_date('20090831','yyyymmdd')
group by caad.approve_one_no,caad.approve_one_name
不要使用in后面跟很多常量的语句,对DB是很大的开销,建议把这些常量都存在一个表中,
2.一次查询的数据量不要太多
and caad.reply_date >= to_date('20090101','yyyymmdd')
and caad.reply_date <= to_date('20090831','yyyymmdd')
将近一年的数据呀,可以分开查询,减少单次查询量,再合并
3。or caad.industry_two_cd in ('371') or caad.industry_trd_cd in ('8920','8910')
使用了or条件就会使得语句不走索引,可以的话把执行计划贴给我看看,那样可以更好的分析瓶颈的所在
强制走选择性最好的那个索引就可以了
比如, caad.report_date 上的单列索引, 或者以该字段为前导列的索引.