案件(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")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
转载于:https://www.cnblogs.com/wanbin/p/9514670.html
