Oracle|利用WITH AS改写SQL Oracle|SQL|优化|sql优化

报表程序中一段SQL语句。
##优化前：

返回：3952
耗时：224s
SQL 代码：

select to_date(nvl(pro.value, '1900-01-01 00:00:00'), 'YYYY-MM-DD HH24:MI:SS') as LAST_UPDATE_TIME, p.productcode as COM_COVERAGE_CODE, p.policyno as POLICY_NO, p.productcode as POLICY_TYPE, nvl(pro3.value, '$$500002000004') as SALES_CHANNEL, p.inceptiondate as EFFECTIVE_DATE, p.terminationdate as EXPIRE_DATE, 1 as INSURED_NO, null as BANK_CODE, to_date(pro1.value, 'YYYY-MM-DD HH24:MI:SS') as APPLICATION_DATE, p.issuedate as ACCEPT_DATE, '05' as SALES_TYPE, p.handler as SALES_CODE, pro2.staffname as SALES_NAME, p.agencyid as SALES_CHANNEL_CODE, pro4.value as SALES_CHANNEL_NAME, pro5.value as BUSINESS_ADDRESS, decode(p.renewalpolicyno, null, '01', '02') as CONTRACT_SOURCE, p.applicationno as CONTRACT_NO, null as RENEWAL_METHOD, p.policystatus as POLICY_STATUS, '0' as REJECTION, p.suminsured as SUM_INSURED, p.suminsured as EFFECTIVE_SUMINSURED, p.premium as PREMIUM, p.premium as CURRENT_PREMIUM, null as YEAR_PREMIUM, case when r4.paymentno > 1 then '09' else '01' end as PAYMENT_METHOD, null as PAYMENT_YEARS, decode(r4.paymentno, 0, 1, r4.paymentno) as PAYMENT_NO, p.productcode as POLICY_HOLDER_PRO, 1 as POLICY_HOLDER_NUM, 1 as EFFECTIVE_INSURED_NUM, p.renewalpolicyno as FORMER_POLICY_NO, pro6.specialremark as SPECIALRE_MARK, null as REGULAR_CLEARING_MARK, null as REGULAR_CLEARING, null as REGULAR_CLEARING_DATE, null as PREMIUMDUE_DATE, '0' as REALTIME_CLAIM_FLAG, '0' as POLICY_LOAN, '0' as AUTO_PAIDUP, p.coinsuranceflag as CO_INSURANCE, p.coinsurancerole as LEAD_CO_INSURANCE, null as CASH_VALUE, null as POLICY_REGISTER_ADDR, null as DEBIT_BANK, null as DEBIT_ACCOUNT, null as DEBIT_NAME from policy p left join role r on p.topactualid = r.topactualid and r.kind = 'DATEINFO' left join property pro on r.topactualid = pro.topactualid and r.actualid = pro.parentactualid and r.parentagreementid = pro.parentagreementid and r.topagreementid = pro.topagreementid and pro.kind = 'UNDERWRITINGDATE' left join property pro1 on pro1.topactualid = p.topactualid and pro1.kind = 'APPLICATIONDATE' left join (select x.policyno as policyno, x.topactualid as topactualid, x.parentactualid as parentactualid, max(decode(x.kind, 'SERIALNO', x.value, '')) as serialno, max(decode(x.kind, 'STAFFNAME', x.value, '')) as staffname from (select p.policynoas policyno, pro.topactualidas topactualid, pro.parentactualid as parentactualid, pro.kindas kind, pro.nameas name, pro.valueas value from policy p left join role r on r.topactualid = p.topactualid and r.kind = 'HANDLER' left join property pro on pro.topactualid = r.topactualid and pro.parentactualid = r.actualid and pro.kind in ('SERIALNO', 'STAFFNAME') where p.productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') and p.policystatus = '$$900001106001' and p.endorsementid is null and p.ISSUEDATE > to_date('20160411', 'YYYYMMDD') and (p.uniquecode like '013100%' or p.uniquecode like '011000%')) x group by policyno, topactualid, parentactualid) pro2 on pro2.policyno = p.policyno and pro2.topactualid = p.topactualid and pro2.serialno = '1' left join role r3 on p.topactualid = r3.topactualid and r3.kind = 'INTERMEDIARYINFO' left join property pro3 on r3.topactualid = pro3.topactualid and r3.actualid = pro3.parentactualid and r3.parentagreementid = pro3.parentagreementid and r3.topagreementid = pro3.topagreementid and pro3.kind = 'INTERMEDIARYTYPE' left join property pro4 on r3.topactualid = pro4.topactualid and r3.actualid = pro4.parentactualid and r3.parentagreementid = pro4.parentagreementid and r3.topagreementid = pro4.topagreementid and pro4.kind = 'INTERMEDIARYNAME' left join property pro5 on r3.topactualid = pro5.topactualid and r3.actualid = pro5.parentactualid and r3.parentagreementid = pro5.parentagreementid and r3.topagreementid = pro5.topagreementid and pro5.kind = 'OFFICEADDRESS' left join (select pro.policyno, pro.topactualid, replace(wm_concat(pro.text), ', ', '?') as specialremark from (select p.policyno, p.topactualid, pro2.value as text from policy p inner join role r on r.topactualid = p.topactualid and r.kind = 'SPECIALAGREEMENTITEM' left join property pro1 on r.topactualid = pro1.topactualid and r.actualid = pro1.parentactualid and r.parentagreementid = pro1.parentagreementid and r.topagreementid = pro1.topagreementid and pro1.kind = 'SERIALNO' left join property pro2 on r.topactualid = pro2.topactualid and r.actualid = pro2.parentactualid and r.parentagreementid = pro2.parentagreementid and r.topagreementid = pro2.topagreementid and pro2.kind = 'SPECIALAGREEMENTTEXT' where p.productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') and p.policystatus = '$$900001106001' and p.endorsementid is null and (p.uniquecode like '013100%' or p.uniquecode like '011000%') and p.ISSUEDATE > to_date('20160411', 'YYYYMMDD') order by pro1.value) pro group by pro.policyno, pro.topactualid) pro6 on p.policyno = pro6.policyno and p.topactualid = pro6.topactualid left join (select p.policyno, p.topactualid, count(*) as paymentno from policy p left join role r on r.topactualid = p.topactualid and r.kind = 'PAYMENTSCHEDULEONAPPLICATION' where p.productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') and p.policystatus = '$$900001106001' and p.endorsementid is null and (p.uniquecode like '013100%' or p.uniquecode like '011000%') and p.ISSUEDATE > to_date('20160411', 'YYYYMMDD') group by p.policyno, p.topactualid) r4 on p.policyno = r4.policyno and p.topactualid = r4.topactualid where p.productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') AND p.policystatus = '$$900001106001' and p.endorsementid is null and (p.uniquecode like '013100%' or p.uniquecode like '011000%') and p.ISSUEDATE > to_date('20160411', 'YYYYMMDD') order by ACCEPT_DATE, POLICY_NOPlan hash value: 3635118867 ---------------------------------------------------------------------------------------------------------------------------- | Id| Operation| Name| Rows| Bytes |TempSpc| Cost (%CPU)| Time| ---------------------------------------------------------------------------------------------------------------------------- |0 | SELECT STATEMENT|| 29315 |127M||367K(1)| 01:13:34 | |1 |SORT ORDER BY|| 29315 |127M|458M|367K(1)| 01:13:34 | |2 |NESTED LOOPS OUTER|| 29315 |127M||340K(1)| 01:08:01 | |3 |NESTED LOOPS OUTER|| 29315 |125M||232K(1)| 00:46:31 | |4 |NESTED LOOPS OUTER||4447 |18M||216K(1)| 00:43:24 | |5 |NESTED LOOPS OUTER||2534 |10M||206K(1)| 00:41:24 | |6 |NESTED LOOPS OUTER||2534 |10M||197K(1)| 00:39:33 | |7 |NESTED LOOPS OUTER||2534 |10M||188K(1)| 00:37:41 | |*8 |HASH JOIN OUTER||2534 |10M|5592K|179K(1)| 00:35:50 | |9 |NESTED LOOPS OUTER||2534 |5557K|| 72919(1)| 00:14:36 | |* 10 |HASH JOIN RIGHT OUTER||1461 |3140K|| 68293(1)| 00:13:40 | |11 |VIEW|| 11415 |367K||6364(1)| 00:01:17 | |12 |HASH GROUP BY|| 11415 |1036K|2360K|6364(1)| 00:01:17 | |* 13 |HASH JOIN OUTER|| 11415 |1036K||6115(1)| 00:01:14 | |* 14 |TABLE ACCESS FULL| POLICY|1461 |98K||4261(1)| 00:00:52 | |* 15 |INDEX RANGE SCAN| IDX_ROLE_TEST2|380K|8907K||1851(1)| 00:00:23 | |* 16 |HASH JOIN OUTER||1461 |3093K|| 61929(1)| 00:12:24 | |* 17 |TABLE ACCESS FULL| POLICY|1461 |208K||4261(1)| 00:00:52 | |18 |VIEW||6221 |11M|| 57668(1)| 00:11:33 | |19 |SORT GROUP BY||6221 |11M|24M| 57668(1)| 00:11:33 | |20 |VIEW||6221 |11M|| 55036(1)| 00:11:01 | |21 |SORT ORDER BY||6221 |1312K|2856K| 55036(1)| 00:11:01 | |22 |NESTED LOOPS OUTER||6221 |1312K|| 54741(1)| 00:10:57 | |23 |NESTED LOOPS OUTER||6221 |1002K|| 31933(1)| 00:06:24 | |24 |NESTED LOOPS||6221 |692K||9125(1)| 00:01:50 | |* 25 |TABLE ACCESS FULL| POLICY|1461 |98K||4261(1)| 00:00:52 | |26 |TABLE ACCESS BY INDEX ROWID| ROLE|4 |180 ||5(0)| 00:00:01 | |* 27 |INDEX RANGE SCAN| IDX_ROLE_TEST2|4 |||3(0)| 00:00:01 | |* 28 |TABLE ACCESS BY INDEX ROWID | PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 29 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2 |1 |||3(0)| 00:00:01 | |* 30 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 31 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2 |1 |||3(0)| 00:00:01 | |32 |TABLE ACCESS BY INDEX ROWID| ROLE|2 |90 ||4(0)| 00:00:01 | |* 33 |INDEX RANGE SCAN| IDX_ROLE_TEST2|1 |||3(0)| 00:00:01 | |34 |VIEW|| 73354 |141M|| 98838(2)| 00:19:47 | |* 35 |FILTER||||||| |36 |HASH GROUP BY|| 73354 |9813K|1092M| 98838(2)| 00:19:47 | |37 |NESTED LOOPS OUTER||7335K|958M|| 18161(1)| 00:03:38 | |38 |NESTED LOOPS OUTER||9913 |968K||9364(1)| 00:01:53 | |* 39 |TABLE ACCESS FULL| POLICY|1461 |98K||4261(1)| 00:00:52 | |40 |TABLE ACCESS BY INDEX ROWID| ROLE|7 |217 ||6(0)| 00:00:01 | |* 41 |INDEX RANGE SCAN| IDX_ROLE_TEST2|7 |||3(0)| 00:00:01 | |42 |VIEW||740 | 27380 ||1(0)| 00:00:01 | |43 |INLIST ITERATOR||||||| |44 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |37 ||6(0)| 00:00:01 | |* 45 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2 |1 |||5(0)| 00:00:01 | |* 46 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 47 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2 |1 |||3(0)| 00:00:01 | |* 48 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 49 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2 |1 |||3(0)| 00:00:01 | |* 50 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 51 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2 |1 |||3(0)| 00:00:01 | |52 |TABLE ACCESS BY INDEX ROWID| PROPERTY|2 |60 ||5(0)| 00:00:01 | |* 53 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2 |1 |||3(0)| 00:00:01 | |54 |TABLE ACCESS BY INDEX ROWID| ROLE|7 |315 ||6(0)| 00:00:01 | |* 55 |INDEX RANGE SCAN| IDX_ROLE_TEST2|7 |||3(0)| 00:00:01 | |* 56 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 57 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2 |1 |||3(0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("PRO2"."TOPACTUALID"(+)="P"."TOPACTUALID" AND "PRO2"."POLICYNO"(+)="P"."POLICYNO") 10 - access("P"."TOPACTUALID"="R4"."TOPACTUALID"(+) AND "P"."POLICYNO"="R4"."POLICYNO"(+)) 13 - access("R"."TOPACTUALID"(+)="P"."TOPACTUALID") 14 - filter("P"."ISSUEDATE">TIMESTAMP' 2016-04-11 00:00:00' AND "P"."ENDORSEMENTID" IS NULL AND ("P"."PRODUCTCODE"='00070001' OR "P"."PRODUCTCODE"='00070002' OR "P"."PRODUCTCODE"='00070003' OR "P"."PRODUCTCODE"='00070004' OR "P"."PRODUCTCODE"='00070005' OR "P"."PRODUCTCODE"='00070006' OR "P"."PRODUCTCODE"='00130001') AND ("P"."UNIQUECODE" LIKE '013100%' OR "P"."UNIQUECODE" LIKE '011000%') AND "P"."POLICYSTATUS"='$$900001106001') 15 - access("R"."KIND"(+)='PAYMENTSCHEDULEONAPPLICATION') 16 - access("P"."TOPACTUALID"="PRO6"."TOPACTUALID"(+) AND "P"."POLICYNO"="PRO6"."POLICYNO"(+)) 17 - filter("P"."ISSUEDATE">TIMESTAMP' 2016-04-11 00:00:00' AND "P"."ENDORSEMENTID" IS NULL AND ("P"."PRODUCTCODE"='00070001' OR "P"."PRODUCTCODE"='00070002' OR "P"."PRODUCTCODE"='00070003' OR "P"."PRODUCTCODE"='00070004' OR "P"."PRODUCTCODE"='00070005' OR "P"."PRODUCTCODE"='00070006' OR "P"."PRODUCTCODE"='00130001') AND ("P"."UNIQUECODE" LIKE '013100%' OR "P"."UNIQUECODE" LIKE '011000%') AND "P"."POLICYSTATUS"='$$900001106001') 25 - filter("P"."ISSUEDATE">TIMESTAMP' 2016-04-11 00:00:00' AND "P"."ENDORSEMENTID" IS NULL AND ("P"."PRODUCTCODE"='00070001' OR "P"."PRODUCTCODE"='00070002' OR "P"."PRODUCTCODE"='00070003' OR "P"."PRODUCTCODE"='00070004' OR "P"."PRODUCTCODE"='00070005' OR "P"."PRODUCTCODE"='00070006' OR "P"."PRODUCTCODE"='00130001') AND ("P"."UNIQUECODE" LIKE '013100%' OR "P"."UNIQUECODE" LIKE '011000%') AND "P"."POLICYSTATUS"='$$900001106001') 27 - access("R"."KIND"='SPECIALAGREEMENTITEM' AND "R"."TOPACTUALID"="P"."TOPACTUALID") 28 - filter("R"."TOPAGREEMENTID"="PRO2"."TOPAGREEMENTID"(+) AND "R"."PARENTAGREEMENTID"="PRO2"."PARENTAGREEMENTID"(+)) 29 - access("PRO2"."KIND"(+)='SPECIALAGREEMENTTEXT' AND "R"."TOPACTUALID"="PRO2"."TOPACTUALID"(+) AND "R"."ACTUALID"="PRO2"."PARENTACTUALID"(+)) 30 - filter("R"."TOPAGREEMENTID"="PRO1"."TOPAGREEMENTID"(+) AND "R"."PARENTAGREEMENTID"="PRO1"."PARENTAGREEMENTID"(+)) 31 - access("PRO1"."KIND"(+)='SERIALNO' AND "R"."TOPACTUALID"="PRO1"."TOPACTUALID"(+) AND "R"."ACTUALID"="PRO1"."PARENTACTUALID"(+)) 33 - access("R3"."KIND"(+)='INTERMEDIARYINFO' AND "P"."TOPACTUALID"="R3"."TOPACTUALID"(+)) 35 - filter(MAX(DECODE("PRO"."KIND",'SERIALNO',"PRO"."VALUE",''))='1') 39 - filter("P"."ISSUEDATE">TIMESTAMP' 2016-04-11 00:00:00' AND "P"."ENDORSEMENTID" IS NULL AND ("P"."PRODUCTCODE"='00070001' OR "P"."PRODUCTCODE"='00070002' OR "P"."PRODUCTCODE"='00070003' OR "P"."PRODUCTCODE"='00070004' OR "P"."PRODUCTCODE"='00070005' OR "P"."PRODUCTCODE"='00070006' OR "P"."PRODUCTCODE"='00130001') AND ("P"."UNIQUECODE" LIKE '013100%' OR "P"."UNIQUECODE" LIKE '011000%') AND "P"."POLICYSTATUS"='$$900001106001') 41 - access("R"."KIND"(+)='HANDLER' AND "R"."TOPACTUALID"(+)="P"."TOPACTUALID") 45 - access(("PRO"."KIND"='SERIALNO' OR "PRO"."KIND"='STAFFNAME') AND "PRO"."TOPACTUALID"="R"."TOPACTUALID" AND "PRO"."PARENTACTUALID"="R"."ACTUALID") 46 - filter("R3"."TOPAGREEMENTID"="PRO3"."TOPAGREEMENTID"(+) AND "R3"."PARENTAGREEMENTID"="PRO3"."PARENTAGREEMENTID"(+)) 47 - access("PRO3"."KIND"(+)='INTERMEDIARYTYPE' AND "R3"."TOPACTUALID"="PRO3"."TOPACTUALID"(+) AND "R3"."ACTUALID"="PRO3"."PARENTACTUALID"(+)) 48 - filter("R3"."TOPAGREEMENTID"="PRO4"."TOPAGREEMENTID"(+) AND "R3"."PARENTAGREEMENTID"="PRO4"."PARENTAGREEMENTID"(+)) 49 - access("PRO4"."KIND"(+)='INTERMEDIARYNAME' AND "R3"."TOPACTUALID"="PRO4"."TOPACTUALID"(+) AND "R3"."ACTUALID"="PRO4"."PARENTACTUALID"(+)) 50 - filter("R3"."TOPAGREEMENTID"="PRO5"."TOPAGREEMENTID"(+) AND "R3"."PARENTAGREEMENTID"="PRO5"."PARENTAGREEMENTID"(+)) 51 - access("PRO5"."KIND"(+)='OFFICEADDRESS' AND "R3"."TOPACTUALID"="PRO5"."TOPACTUALID"(+) AND "R3"."ACTUALID"="PRO5"."PARENTACTUALID"(+)) 53 - access("PRO1"."KIND"(+)='APPLICATIONDATE' AND "PRO1"."TOPACTUALID"(+)="P"."TOPACTUALID") 55 - access("R"."KIND"(+)='DATEINFO' AND "P"."TOPACTUALID"="R"."TOPACTUALID"(+)) 56 - filter("R"."TOPAGREEMENTID"="PRO"."TOPAGREEMENTID"(+) AND "R"."PARENTAGREEMENTID"="PRO"."PARENTAGREEMENTID"(+)) 57 - access("PRO"."KIND"(+)='UNDERWRITINGDATE' AND "R"."TOPACTUALID"="PRO"."TOPACTUALID"(+) AND "R"."ACTUALID"="PRO"."PARENTACTUALID"(+))

##分析
分析SQL代码，可以看出访问多次policy表，谓词条件也一样

left join (select x.policyno as policyno, x.topactualid as topactualid, x.parentactualid as parentactualid, max(decode(x.kind, 'SERIALNO', x.value, '')) as serialno, max(decode(x.kind, 'STAFFNAME', x.value, '')) as staffname from (select p.policynoas policyno, pro.topactualidas topactualid, pro.parentactualid as parentactualid, pro.kindas kind, pro.nameas name, pro.valueas value from policy p left join role r on r.topactualid = p.topactualid and r.kind = 'HANDLER' left join property pro on pro.topactualid = r.topactualid and pro.parentactualid = r.actualid and pro.kind in ('SERIALNO', 'STAFFNAME') where p.productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') and p.policystatus = '$$900001106001' and p.endorsementid is null and p.ISSUEDATE > to_date('20160411', 'YYYYMMDD') and (p.uniquecode like '013100%' or p.uniquecode like '011000%')) x group by policyno, topactualid, parentactualid) pro2 ----------------------------------------------------------------------- left join (select pro.policyno, pro.topactualid, replace(wm_concat(pro.text), ', ', '?') as specialremark from (select p.policyno, p.topactualid, pro2.value as text from policy p inner join role r on r.topactualid = p.topactualid and r.kind = 'SPECIALAGREEMENTITEM' left join property pro1 on r.topactualid = pro1.topactualid and r.actualid = pro1.parentactualid and r.parentagreementid = pro1.parentagreementid and r.topagreementid = pro1.topagreementid and pro1.kind = 'SERIALNO' left join property pro2 on r.topactualid = pro2.topactualid and r.actualid = pro2.parentactualid and r.parentagreementid = pro2.parentagreementid and r.topagreementid = pro2.topagreementid and pro2.kind = 'SPECIALAGREEMENTTEXT' where p.productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') and p.policystatus = '$$900001106001' and p.endorsementid is null and (p.uniquecode like '013100%' or p.uniquecode like '011000%') and p.ISSUEDATE > to_date('20160411', 'YYYYMMDD') order by pro1.value) pro group by pro.policyno, pro.topactualid) pro6------------------------------------------------------------------- left join (select p.policyno, p.topactualid, count(*) as paymentno from policy p left join role r on r.topactualid = p.topactualid and r.kind = 'PAYMENTSCHEDULEONAPPLICATION' where p.productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') and p.policystatus = '$$900001106001' and p.endorsementid is null and (p.uniquecode like '013100%' or p.uniquecode like '011000%') and p.ISSUEDATE > to_date('20160411', 'YYYYMMDD') group by p.policyno, p.topactualid) r4

##优化

耗时：10s
返回：3952

【Oracle|利用WITH AS改写SQL】利用with as 先把访问policy表数据提取并缓存成临时表，然后再进行关联查询。

with p as (select policyno,topactualid from policy where productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') and policystatus = '$$900001106001' and endorsementid is null and (uniquecode like '013100%' or uniquecode like '011000%') and ISSUEDATE > to_date('20160411', 'YYYYMMDD')) select to_date(nvl(pro.value, '1900-01-01 00:00:00'), 'YYYY-MM-DD HH24:MI:SS') as LAST_UPDATE_TIME, p.productcode as COM_COVERAGE_CODE, p.policyno as POLICY_NO, p.productcode as POLICY_TYPE, nvl(pro3.value, '$$500002000004') as SALES_CHANNEL, p.inceptiondate as EFFECTIVE_DATE, p.terminationdate as EXPIRE_DATE, 1 as INSURED_NO, null as BANK_CODE, to_date(pro1.value, 'YYYY-MM-DD HH24:MI:SS') as APPLICATION_DATE, p.issuedate as ACCEPT_DATE, '05' as SALES_TYPE, p.handler as SALES_CODE, pro2.staffname as SALES_NAME, p.agencyid as SALES_CHANNEL_CODE, pro4.value as SALES_CHANNEL_NAME, pro5.value as BUSINESS_ADDRESS, decode(p.renewalpolicyno, null, '01', '02') as CONTRACT_SOURCE, p.applicationno as CONTRACT_NO, null as RENEWAL_METHOD, p.policystatus as POLICY_STATUS, '0' as REJECTION, p.suminsured as SUM_INSURED, p.suminsured as EFFECTIVE_SUMINSURED, p.premium as PREMIUM, p.premium as CURRENT_PREMIUM, null as YEAR_PREMIUM, case when r4.paymentno > 1 then '09' else '01' end as PAYMENT_METHOD, null as PAYMENT_YEARS, decode(r4.paymentno, 0, 1, r4.paymentno) as PAYMENT_NO, p.productcode as POLICY_HOLDER_PRO, 1 as POLICY_HOLDER_NUM, 1 as EFFECTIVE_INSURED_NUM, p.renewalpolicyno as FORMER_POLICY_NO, pro6.specialremark as SPECIALRE_MARK, null as REGULAR_CLEARING_MARK, null as REGULAR_CLEARING, null as REGULAR_CLEARING_DATE, null as PREMIUMDUE_DATE, '0' as REALTIME_CLAIM_FLAG, '0' as POLICY_LOAN, '0' as AUTO_PAIDUP, p.coinsuranceflag as CO_INSURANCE, p.coinsurancerole as LEAD_CO_INSURANCE, null as CASH_VALUE, null as POLICY_REGISTER_ADDR, null as DEBIT_BANK, null as DEBIT_ACCOUNT, null as DEBIT_NAME from policy p left join role r on p.topactualid = r.topactualid and r.kind = 'DATEINFO' left join property pro on r.topactualid = pro.topactualid and r.actualid = pro.parentactualid and r.parentagreementid = pro.parentagreementid and r.topagreementid = pro.topagreementid and pro.kind = 'UNDERWRITINGDATE' left join property pro1 on pro1.topactualid = p.topactualid and pro1.kind = 'APPLICATIONDATE' left join (select x.policyno as policyno, x.topactualid as topactualid, x.parentactualid as parentactualid, max(decode(x.kind, 'SERIALNO', x.value, '')) as serialno, max(decode(x.kind, 'STAFFNAME', x.value, '')) as staffname from (select p.policynoas policyno, pro.topactualidas topactualid, pro.parentactualid as parentactualid, pro.kindas kind, pro.nameas name, pro.valueas value fromp left join role r on r.topactualid = p.topactualid and r.kind = 'HANDLER' left join property pro on pro.topactualid = r.topactualid and pro.parentactualid = r.actualid and pro.kind in ('SERIALNO', 'STAFFNAME')) x group by policyno, topactualid, parentactualid) pro2 on pro2.policyno = p.policyno and pro2.topactualid = p.topactualid and pro2.serialno = '1' left join role r3 on p.topactualid = r3.topactualid and r3.kind = 'INTERMEDIARYINFO' left join property pro3 on r3.topactualid = pro3.topactualid and r3.actualid = pro3.parentactualid and r3.parentagreementid = pro3.parentagreementid and r3.topagreementid = pro3.topagreementid and pro3.kind = 'INTERMEDIARYTYPE' left join property pro4 on r3.topactualid = pro4.topactualid and r3.actualid = pro4.parentactualid and r3.parentagreementid = pro4.parentagreementid and r3.topagreementid = pro4.topagreementid and pro4.kind = 'INTERMEDIARYNAME' left join property pro5 on r3.topactualid = pro5.topactualid and r3.actualid = pro5.parentactualid and r3.parentagreementid = pro5.parentagreementid and r3.topagreementid = pro5.topagreementid and pro5.kind = 'OFFICEADDRESS' left join (select pro.policyno, pro.topactualid, replace(wm_concat(pro.text), ', ', '?') as specialremark from (select p.policyno, p.topactualid, pro2.value as text fromp inner join role r on r.topactualid = p.topactualid and r.kind = 'SPECIALAGREEMENTITEM' left join property pro1 on r.topactualid = pro1.topactualid and r.actualid = pro1.parentactualid and r.parentagreementid = pro1.parentagreementid and r.topagreementid = pro1.topagreementid and pro1.kind = 'SERIALNO' left join property pro2 on r.topactualid = pro2.topactualid and r.actualid = pro2.parentactualid and r.parentagreementid = pro2.parentagreementid and r.topagreementid = pro2.topagreementid and pro2.kind = 'SPECIALAGREEMENTTEXT' order by pro1.value) pro group by pro.policyno, pro.topactualid) pro6 on p.policyno = pro6.policyno and p.topactualid = pro6.topactualid left join (select p.policyno, p.topactualid, count(*) as paymentno fromp left join role r on r.topactualid = p.topactualid and r.kind = 'PAYMENTSCHEDULEONAPPLICATION' group by p.policyno, p.topactualid) r4 on p.policyno = r4.policyno and p.topactualid = r4.topactualid where p.productcode in ('00070001', '00070002', '00070003', '00070004', '00070005', '00070006', '00130001') AND p.policystatus = '$$900001106001' and p.endorsementid is null and (p.uniquecode like '013100%' or p.uniquecode like '011000%') and p.ISSUEDATE > to_date('20160411', 'YYYYMMDD') order by ACCEPT_DATE, POLICY_NO; Plan hash value: 3669690643 -------------------------------------------------------------------------------------------------------------------------------------- | Id| Operation| Name| Rows| Bytes |TempSpc| Cost (%CPU)| Time| -------------------------------------------------------------------------------------------------------------------------------------- |0 | SELECT STATEMENT|| 29315 |127M||332K(1)| 01:06:29 | |1 |TEMP TABLE TRANSFORMATION||||||| |2 |LOAD AS SELECT||||||| |*3 |TABLE ACCESS FULL| POLICY|1461 |98K||4261(1)| 00:00:52 | |4 |SORT ORDER BY|| 29315 |127M|458M|328K(1)| 01:05:37 | |5 |NESTED LOOPS OUTER|| 29315 |127M||300K(1)| 01:00:04 | |6 |NESTED LOOPS OUTER|| 29315 |125M||192K(1)| 00:38:34 | |7 |NESTED LOOPS OUTER||4447 |18M||177K(1)| 00:35:28 | |8 |NESTED LOOPS OUTER||2534 |10M||167K(1)| 00:33:28 | |9 |NESTED LOOPS OUTER||2534 |10M||157K(1)| 00:31:36 | |10 |NESTED LOOPS OUTER||2534 |10M||148K(1)| 00:29:45 | |* 11 |HASH JOIN OUTER||2534 |10M|5592K|139K(1)| 00:27:53 | |12 |NESTED LOOPS OUTER||2534 |5557K|| 64290(1)| 00:12:52 | |* 13 |HASH JOIN RIGHT OUTER||1461 |3140K|| 59665(1)| 00:11:56 | |14 |VIEW|| 11414 |367K||1991(1)| 00:00:24 | |15 |HASH GROUP BY|| 11414 |490K|1176K|1991(1)| 00:00:24 | |* 16 |HASH JOIN OUTER|| 11414 |490K||1860(1)| 00:00:23 | |17 |VIEW||1461 | 29220 ||5(0)| 00:00:01 | |18 |TABLE ACCESS FULL| SYS_TEMP_0FD9D6613_773E7914 |1461 | 29220 ||5(0)| 00:00:01 | |* 19 |INDEX RANGE SCAN| IDX_ROLE_TEST2|380K|8907K||1851(1)| 00:00:23 | |* 20 |HASH JOIN OUTER||1461 |3093K|| 57674(1)| 00:11:33 | |* 21 |TABLE ACCESS FULL| POLICY|1461 |208K||4261(1)| 00:00:52 | |22 |VIEW||6221 |11M|| 53413(1)| 00:10:41 | |23 |SORT GROUP BY||6221 |11M|24M| 53413(1)| 00:10:41 | |24 |VIEW||6221 |11M|| 50780(1)| 00:10:10 | |25 |SORT ORDER BY||6221 |1312K|2856K| 50780(1)| 00:10:10 | |26 |NESTED LOOPS OUTER||6221 |1312K|| 50485(1)| 00:10:06 | |27 |NESTED LOOPS OUTER||6221 |1002K|| 27677(1)| 00:05:33 | |28 |NESTED LOOPS||6221 |692K||4869(1)| 00:00:59 | |29 |VIEW||1461 |98K||5(0)| 00:00:01 | |30 |TABLE ACCESS FULL| SYS_TEMP_0FD9D6613_773E7914 |1461 | 29220 ||5(0)| 00:00:01 | |31 |TABLE ACCESS BY INDEX ROWID| ROLE|4 |180 ||5(0)| 00:00:01 | |* 32 |INDEX RANGE SCAN| IDX_ROLE_TEST2|4 |||3(0)| 00:00:01 | |* 33 |TABLE ACCESS BY INDEX ROWID | PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 34 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2|1 |||3(0)| 00:00:01 | |* 35 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 36 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2|1 |||3(0)| 00:00:01 | |37 |TABLE ACCESS BY INDEX ROWID| ROLE|2 |90 ||4(0)| 00:00:01 | |* 38 |INDEX RANGE SCAN| IDX_ROLE_TEST2|1 |||3(0)| 00:00:01 | |39 |VIEW|| 73346 |141M|| 67762(2)| 00:13:34 | |* 40 |FILTER||||||| |41 |HASH GROUP BY|| 73346 |6303K|714M| 67762(2)| 00:13:34 | |42 |NESTED LOOPS OUTER||7334K|615M|| 13903(1)| 00:02:47 | |43 |NESTED LOOPS OUTER||9912 |493K||5109(1)| 00:01:02 | |44 |VIEW||1461 | 29220 ||5(0)| 00:00:01 | |45 |TABLE ACCESS FULL| SYS_TEMP_0FD9D6613_773E7914 |1461 | 29220 ||5(0)| 00:00:01 | |46 |TABLE ACCESS BY INDEX ROWID| ROLE|7 |217 ||6(0)| 00:00:01 | |* 47 |INDEX RANGE SCAN| IDX_ROLE_TEST2|7 |||3(0)| 00:00:01 | |48 |VIEW||740 | 27380 ||1(0)| 00:00:01 | |49 |INLIST ITERATOR||||||| |50 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |37 ||6(0)| 00:00:01 | |* 51 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2|1 |||5(0)| 00:00:01 | |* 52 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 53 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2|1 |||3(0)| 00:00:01 | |* 54 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 55 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2|1 |||3(0)| 00:00:01 | |* 56 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 57 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2|1 |||3(0)| 00:00:01 | |58 |TABLE ACCESS BY INDEX ROWID| PROPERTY|2 |60 ||5(0)| 00:00:01 | |* 59 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2|1 |||3(0)| 00:00:01 | |60 |TABLE ACCESS BY INDEX ROWID| ROLE|7 |315 ||6(0)| 00:00:01 | |* 61 |INDEX RANGE SCAN| IDX_ROLE_TEST2|7 |||3(0)| 00:00:01 | |* 62 |TABLE ACCESS BY INDEX ROWID| PROPERTY|1 |51 ||4(0)| 00:00:01 | |* 63 |INDEX RANGE SCAN| IDX_PROPERTY_TEST2|1 |||3(0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("ISSUEDATE">TIMESTAMP' 2016-04-11 00:00:00' AND "ENDORSEMENTID" IS NULL AND ("PRODUCTCODE"='00070001' OR "PRODUCTCODE"='00070002' OR "PRODUCTCODE"='00070003' OR "PRODUCTCODE"='00070004' OR "PRODUCTCODE"='00070005' OR "PRODUCTCODE"='00070006' OR "PRODUCTCODE"='00130001') AND ("UNIQUECODE" LIKE '013100%' OR "UNIQUECODE" LIKE '011000%') AND "POLICYSTATUS"='$$900001106001') 11 - access("PRO2"."TOPACTUALID"(+)="P"."TOPACTUALID" AND "PRO2"."POLICYNO"(+)="P"."POLICYNO") 13 - access("P"."TOPACTUALID"="R4"."TOPACTUALID"(+) AND "P"."POLICYNO"="R4"."POLICYNO"(+)) 16 - access("R"."TOPACTUALID"(+)="P"."TOPACTUALID") 19 - access("R"."KIND"(+)='PAYMENTSCHEDULEONAPPLICATION') 20 - access("P"."TOPACTUALID"="PRO6"."TOPACTUALID"(+) AND "P"."POLICYNO"="PRO6"."POLICYNO"(+)) 21 - filter("P"."ISSUEDATE">TIMESTAMP' 2016-04-11 00:00:00' AND "P"."ENDORSEMENTID" IS NULL AND ("P"."PRODUCTCODE"='00070001' OR "P"."PRODUCTCODE"='00070002' OR "P"."PRODUCTCODE"='00070003' OR "P"."PRODUCTCODE"='00070004' OR "P"."PRODUCTCODE"='00070005' OR "P"."PRODUCTCODE"='00070006' OR "P"."PRODUCTCODE"='00130001') AND ("P"."UNIQUECODE" LIKE '013100%' OR "P"."UNIQUECODE" LIKE '011000%') AND "P"."POLICYSTATUS"='$$900001106001') 32 - access("R"."KIND"='SPECIALAGREEMENTITEM' AND "R"."TOPACTUALID"="P"."TOPACTUALID") 33 - filter("R"."TOPAGREEMENTID"="PRO2"."TOPAGREEMENTID"(+) AND "R"."PARENTAGREEMENTID"="PRO2"."PARENTAGREEMENTID"(+)) 34 - access("PRO2"."KIND"(+)='SPECIALAGREEMENTTEXT' AND "R"."TOPACTUALID"="PRO2"."TOPACTUALID"(+) AND "R"."ACTUALID"="PRO2"."PARENTACTUALID"(+)) 35 - filter("R"."TOPAGREEMENTID"="PRO1"."TOPAGREEMENTID"(+) AND "R"."PARENTAGREEMENTID"="PRO1"."PARENTAGREEMENTID"(+)) 36 - access("PRO1"."KIND"(+)='SERIALNO' AND "R"."TOPACTUALID"="PRO1"."TOPACTUALID"(+) AND "R"."ACTUALID"="PRO1"."PARENTACTUALID"(+)) 38 - access("R3"."KIND"(+)='INTERMEDIARYINFO' AND "P"."TOPACTUALID"="R3"."TOPACTUALID"(+)) 40 - filter(MAX(DECODE("PRO"."KIND",'SERIALNO',"PRO"."VALUE",''))='1') 47 - access("R"."KIND"(+)='HANDLER' AND "R"."TOPACTUALID"(+)="P"."TOPACTUALID") 51 - access(("PRO"."KIND"='SERIALNO' OR "PRO"."KIND"='STAFFNAME') AND "PRO"."TOPACTUALID"="R"."TOPACTUALID" AND "PRO"."PARENTACTUALID"="R"."ACTUALID") 52 - filter("R3"."TOPAGREEMENTID"="PRO3"."TOPAGREEMENTID"(+) AND "R3"."PARENTAGREEMENTID"="PRO3"."PARENTAGREEMENTID"(+)) 53 - access("PRO3"."KIND"(+)='INTERMEDIARYTYPE' AND "R3"."TOPACTUALID"="PRO3"."TOPACTUALID"(+) AND "R3"."ACTUALID"="PRO3"."PARENTACTUALID"(+)) 54 - filter("R3"."TOPAGREEMENTID"="PRO4"."TOPAGREEMENTID"(+) AND "R3"."PARENTAGREEMENTID"="PRO4"."PARENTAGREEMENTID"(+)) 55 - access("PRO4"."KIND"(+)='INTERMEDIARYNAME' AND "R3"."TOPACTUALID"="PRO4"."TOPACTUALID"(+) AND "R3"."ACTUALID"="PRO4"."PARENTACTUALID"(+)) 56 - filter("R3"."TOPAGREEMENTID"="PRO5"."TOPAGREEMENTID"(+) AND "R3"."PARENTAGREEMENTID"="PRO5"."PARENTAGREEMENTID"(+)) 57 - access("PRO5"."KIND"(+)='OFFICEADDRESS' AND "R3"."TOPACTUALID"="PRO5"."TOPACTUALID"(+) AND "R3"."ACTUALID"="PRO5"."PARENTACTUALID"(+)) 59 - access("PRO1"."KIND"(+)='APPLICATIONDATE' AND "PRO1"."TOPACTUALID"(+)="P"."TOPACTUALID") 61 - access("R"."KIND"(+)='DATEINFO' AND "P"."TOPACTUALID"="R"."TOPACTUALID"(+)) 62 - filter("R"."TOPAGREEMENTID"="PRO"."TOPAGREEMENTID"(+) AND "R"."PARENTAGREEMENTID"="PRO"."PARENTAGREEMENTID"(+)) 63 - access("PRO"."KIND"(+)='UNDERWRITINGDATE' AND "R"."TOPACTUALID"="PRO"."TOPACTUALID"(+) AND "R"."ACTUALID"="PRO"."PARENTACTUALID"(+))