背景
案件(13405_RI-再保结算查询导出太慢)，造成性能慢的原因是执行以下sql时，每次执行平均需要消耗
2秒，画面上，点击一次导出按钮，就会发起数以百记的调用。
SQL执行代码段
select * from t_policy_payment_writeoff where policysn = 'PB12345';
t_policy_payment_writeoff为视图，其代码为：
create or replace view t_policy_payment_writeoff as
select rownum as id, t."WRITEOFFAMOUT",t."POLICYSN"
from (
select sum(b.realamount) writeoffamout, b.custseq policysn
--保费回写视图
from ciod_bp.mm_writeoutstatus_to a, ciod_bp.mm_writeout_to b
where a.id = b.id
and a.datasource = 'RI'
and a.status = '99'
and a.writeouttype = '1' --回写成功的..
group by b.custseq
union all
--分入转分出的业务，分出的账单找对应的分入账单的保费
select max(cat_parent.acc_writeoffsum) writeoffamout, fin.policysn
from ciod_ri.account_catalog cat_self,
ciod_ri.account_catalog cat_parent,
ciod_ri.ri_fac_cont_reins_fin fin
where cat_self.premium_bill_no = cat_parent.bill_no
and cat_parent.bill_no = fin.statement_no
and cat_self.retrocession_flag = 'T'
and cat_parent.acc_writeoffsum <> 0
group by fin.policysn
) t
;
SQL执行计划为
Plan hash value: 3741877562
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 458K| 64M| | 27972 (2)| 00:05:36 |
|* 1 | VIEW | T_POLICY_PAYMENT_WRITEOFF | 458K| 64M| | 27972 (2)| 00:05:36 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 458K| 59M| | 27972 (2)| 00:05:36 |
| 4 | UNION-ALL | | | | | | |
| 5 | HASH GROUP BY | | 458K| 24M| 59M| 23812 (2)| 00:04:46 |
|* 6 | HASH JOIN | | 458K| 24M| 12M| 17447 (2)| 00:03:30 |
|* 7 | TABLE ACCESS FULL | MM_WRITEOUTSTATUS_TO | 458K| 7159K| | 2938 (2)| 00:00:36 |
| 8 | TABLE ACCESS FULL | MM_WRITEOUT_TO | 1241K| 48M| | 10757 (2)| 00:02:10 |
| 9 | HASH GROUP BY | | 795 | 81090 | | 4161 (1)| 00:00:50 |
|* 10 | HASH JOIN | | 795 | 81090 | | 4160 (1)| 00:00:50 |
| 11 | NESTED LOOPS | | 795 | 38955 | | 3683 (1)| 00:00:45 |
|* 12 | TABLE ACCESS FULL | ACCOUNT_CATALOG | 724 | 2896 | | 2234 (2)| 00:00:27 |
|* 13 | TABLE ACCESS BY INDEX ROWID| ACCOUNT_CATALOG | 1 | 45 | | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_ACCOUNT_CATALOG | 1 | | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | RI_FAC_CONT_REINS_FIN | 93390 | 4833K| | 475 (2)| 00:00:06 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("POLICYSN"='PB12345')
6 - access("A"."ID"="B"."ID")
7 - filter("A"."WRITEOUTTYPE"='1' AND "A"."DATASOURCE"='RI' AND "A"."STATUS"='99')
10 - access("CAT_PARENT"."BILL_NO"="FIN"."STATEMENT_NO")
12 - filter("CAT_SELF"."PREMIUM_BILL_NO" IS NOT NULL AND "CAT_SELF"."RETROCESSION_FLAG"='T')
13 - filter("CAT_PARENT"."ACC_WRITEOFFSUM"<>0)
14 - access("CAT_SELF"."PREMIUM_BILL_NO"="CAT_PARENT"."BILL_NO")
分析
根据执行计划中，ID=7与ID=15,MM_WRITEOUTSTATUS_TO与RI_FAC_CONT_REINS_FIN 是全表扫描方式去访问表。
谓词条件是 where policysn = 'PB12345';
然后我们可以在MM_WRITEOUTSTATUS_TO表的字段CUSTSEQ建立索引
RI_FAC_CONT_REINS_FIN表的字段POLICYSN建立索引，使其能进行谓词推入，并进行索引访问。
但是视图t_policy_payment_writeoff 有rownum 字段，会阻止进行谓词推入，所以我们可以改写该视图。
优化
1）增加索引
create index CIOD_BP.IDX_CUSTSEQ_1 on CIOD_BP.MM_WRITEOUT_TO (CUSTSEQ);
create index ciod_ri.IDX_POLICYSN_1 on ciod_ri.RI_FAC_CONT_REINS_FIN (POLICYSN);2）改写视图,去掉rownum as id 字段
create or replace view t_policy_payment_writeoff as
select t."WRITEOFFAMOUT",t."POLICYSN"
from (
select sum(b.realamount) writeoffamout, b.custseq policysn
--保费回写视图
from ciod_bp.mm_writeoutstatus_to a, ciod_bp.mm_writeout_to b
where a.id = b.id
and a.datasource = 'RI'
and a.status = '99'
and a.writeouttype = '1' --回写成功的..
group by b.custseq
union all
--分入转分出的业务，分出的账单找对应的分入账单的保费
select max(cat_parent.acc_writeoffsum) writeoffamout, fin.policysn
from ciod_ri.account_catalog cat_self,
ciod_ri.account_catalog cat_parent,
ciod_ri.ri_fac_cont_reins_fin fin
where cat_self.premium_bill_no = cat_parent.bill_no
and cat_parent.bill_no = fin.statement_no
and cat_self.retrocession_flag = 'T'
and cat_parent.acc_writeoffsum <> 0
group by fin.policysn
) t3）原sql改成
select rownum as id,b.* from t_policy_payment_writeoff b where policysn = 'PB12345';
#执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 270 | 2252 (2)| 00:00:28 |
| 1 | VIEW | | 2 | 270 | 2252 (2)| 00:00:28 |
| 2 | UNION-ALL | | | | | |
| 3 | SORT GROUP BY NOSORT | | 1 | 57 | 11 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUTSTATUS_TO | 1 | 16 | 3 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 114 | 11 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| MM_WRITEOUT_TO | 2 | 82 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_CUSTSEQ_1 | 2 | | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_1 | 1 | | 2 (0)| 00:00:01 |
| 9 | SORT GROUP BY NOSORT | | 1 | 102 | 2241 (2)| 00:00:27 |
|* 10 | HASH JOIN | | 1 | 102 | 2241 (2)| 00:00:27 |
| 11 | NESTED LOOPS | | 2 | 196 | 7 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| RI_FAC_CONT_REINS_FIN | 2 | 106 | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_POLICYSN_1 | 2 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| ACCOUNT_CATALOG | 1 | 45 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_ACCOUNT_CATALOG | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | ACCOUNT_CATALOG | 724 | 2896 | 2234 (2)| 00:00:27 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."WRITEOUTTYPE"='1' AND "A"."DATASOURCE"='RI' AND "A"."STATUS"='99')
7 - access("B"."CUSTSEQ"='PB12345')
8 - access("A"."ID"="B"."ID")
10 - access("CAT_SELF"."PREMIUM_BILL_NO"="CAT_PARENT"."BILL_NO")
13 - access("FIN"."POLICYSN"='PB12345')
14 - filter("CAT_PARENT"."ACC_WRITEOFFSUM"<>0)
15 - access("CAT_PARENT"."BILL_NO"="FIN"."STATEMENT_NO")
16 - filter("CAT_SELF"."PREMIUM_BILL_NO" IS NOT NULL AND "CAT_SELF"."RETROCESSION_FLAG"='T')优化后
SQL运行时间降至：0.034s