-- SQL WITH page_ids AS (SELECT * FROM (SELECT A.*, rownum num FROM (SELECT /*+ index(a ADMINFILEINFO_CONTNO)*/ a.adminfileid from adminfileinfo a LEFT JOIN lccont l on a.contno = l.contno and a.managecom = l.managecom LEFT JOIN PolicyContactInfo r on (r.GrpContNo = a.GrpContNo AND r.ManageCom = a.ManageCom AND (r.contno = a.contno or r.contno = '0000000000')) LEFT JOIN lcinseuredinfo c on (a.contno = c.contno and a.managecom = c.managecom) LEFT JOIN LCRenewalPaymentResult lcp on (lcp.contno = a.contno and lcp.paytodate = a.ljsdate) LEFT JOIN mi_lmriskapp lm on a.mainpolcode = lm.riskCode where 1 = 1 and a.BatchState = '1' and a.adminfiletype = '2' -- 这里传值不一样 and l.Servcode = 'xx' and ((a.contsource = 'EBA' and ((r.appnttype = '1' and a.paysource = '01') or a.paysource = '02')) or a.contsource = 'CSC') order by a.cinvalidate, a.managecom, a.grpcontno, a.contno ) A where rownum <= 5) where num > 0) SELECT to_char(a.adminfileid), to_char(a.contno), to_char(a.contsource), a.BusinessType BusinessType, a.cvalidate, a.ljsdate, to_char(a.prem), a.paysource paysource, a.cinvalidate, to_char(c.idno), to_char(c.name), to_char((case when r.GrpType = '2' then r.ContactName ELSE r.AppntName end)) AppntName, to_char(r.idno) idnos, to_char((SELECT * FROM (SELECT paydesire from desireinfo where a.AdminfileID = BatchID and operatortype in ('1', '3') order by ModifyDate desc, ModifyTime desc) WHERE rownum = 1)) paydesire, to_char(l.ServCode), to_char(l.ServName), to_char((case when to_number(a.payperiod) > 1 then '长险' else '短险' end)) payPeriod, to_char(ceil(TO_NUMBER(re.CInvaliDate - sysdate))) leftDays, lr.riskname, to_char(c.sex), to_char((case when r.appnttype = '2' then '法人' when r.appnttype = '1' then '自然人' else '' end)) Appnttype, a.LastTransferDate, a.LastTransferResult resultTran, a.signstate signname, to_char(re.CInvaliDate - 1, 'YYYY-MM-DD') leftDate, c.mainContNo, c.maininseuredname, to_char(a.managecom), nvl((select plcy_level from (select plcy_level from mi_customerfeeinfo where is_main_risk = 'Y' and contno = a.contno order by payduedate desc) where rownum = 1), '') plcy_level, to_char(a.mainpolcode), (case when r.grptype = '1' then '0' else '1' end) isPersonalName, lcp.prestoreResult prestoreResult, l.taienjoyriskxq txFlag from page_ids p left join adminfileinfo a on (p.adminfileid = a.adminfileid) LEFT JOIN lccont l on a.contno = l.contno and a.managecom = l.managecom LEFT JOIN PolicyContactInfo r on (r.GrpContNo = a.GrpContNo and r.ManageCom = a.ManageCom and (r.contno = a.contno or r.contno = '0000000000')) LEFT JOIN lcinseuredinfo c on (a.contno = c.contno and a.managecom = c.managecom) LEFT JOIN LCRenewalPaymentResult lcp on (lcp.contno = a.contno and lcp.paytodate = a.ljsdate) left join lmriskname lr on (lr.risksource = a.contsource and lr.riskcode = a.mainpolcode) left join receivableriskinfo re on (re.adminfileid = a.adminfileid and re.POLNO = a.mainpolno) -- 原始执行计划 -- 感觉是A表估行不准，导致得驱动表选择错误 但不知道为什么A表差这么多，这几张表得统计信息都手动收集了 COLUMN_VALUE | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ =================================================================================================================================================================================================================| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|| -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |0 |SUBPLAN FILTER | |5 |11057 |3 |97513638 |0 |236 || |1 |├─NESTED-LOOP OUTER JOIN | |5 |10835 |3 |97513638 |0 |57 || |2 |│ ├─NESTED-LOOP OUTER JOIN | |5 |10743 |3 |97513638 |0 |26 || |3 |│ │ ├─NESTED-LOOP OUTER JOIN | |5 |10652 |3 |97513638 |0 |62 || |4 |│ │ │ ├─NESTED-LOOP OUTER JOIN | |5 |10561 |3 |97513638 |0 |30 || |5 |│ │ │ │ ├─NESTED-LOOP OUTER JOIN | |5 |10470 |3 |97513638 |0 |113 || |6 |│ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |5 |10378 |3 |97513638 |0 |96 || |7 |│ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |5 |10255 |3 |97513638 |0 |63 || |8 |│ │ │ │ │ │ │ ├─SUBPLAN SCAN |VIEW1 |5 |10163 |3 |97513638 |0 |6 || |9 |│ │ │ │ │ │ │ │ └─LIMIT | |5 |10163 |3 |97513638 |0 |9 || |10|│ │ │ │ │ │ │ │ └─TOP-N SORT | |5 |10163 |3 |97513638 |0 |62 || |11|│ │ │ │ │ │ │ │ └─NESTED-LOOP OUTER JOIN | |5 |10162 |3 |97513638 |0 |92 || |12|│ │ │ │ │ │ │ │ ├─NESTED-LOOP JOIN | |6 |10063 |3 |97513638 |0 |207798 || |13|│ │ │ │ │ │ │ │ │ ├─TABLE FULL SCAN |A(IDX_ADMINFILEINFO_CINVALIDATE_MANAGECOM_GRPCONTNO) |7 |9938 |39589 |97513638 |0 |87403687 || |14|│ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN|L(IDX_LCCONT_SMCCC) |1 |18 |3 |1525141 |0 |148505 || |15|│ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |R |1 |18 |3 |1317751 |0 |168 || |16|│ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |A |1 |18 |3 |97513638 |0 |86 || |17|│ │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |L(LCCONT_INDEX_2) |1 |24 |3 |97513638 |0 |163 || |18|│ │ │ │ │ └─DISTRIBUTED TABLE GET |R |1 |18 |3 |97513638 |0 |70 || |19|│ │ │ │ └─DISTRIBUTED TABLE GET |C |1 |18 |3 |97513638 |0 |31 || |20|│ │ │ └─DISTRIBUTED TABLE GET |LCP |1 |18 |0 |97515753 |0 |55 || |21|│ │ └─DISTRIBUTED TABLE GET |LR |1 |18 |3 |97513638 |0 |26 || |22|│ └─DISTRIBUTED TABLE RANGE SCAN |RE |1 |18 |0 |97515753 |0 |102 || |23|├─TOP-N SORT | |1 |21 |3 |97513638 |0 |50 || |24|│ └─DISTRIBUTED TABLE RANGE SCAN |DESIREINFO |1 |21 |3 |97513638 |0 |96 || |25|└─DISTRIBUTED TABLE RANGE SCAN |MI_CUSTOMERFEEINFO(IDX_MI_CUSTOMERFEEINFO_CONTNO_MAIN_RISK_PAYDUEDATE,Reverse)|1 |25 |2 |97513638 |0 |123 || =================================================================================================================================================================================================================| -- 加上/*+ index(a ADMINFILEINFO_CONTNO)*/hint之后的执行计划 或者l.servcode传A值也是这个执行计划 COLUMN_VALUE | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ =================================================================================================================================================================================================================| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|| -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |0 |SUBPLAN FILTER | |5 |965405 |3 |508020 |0 |0 || |1 |├─NESTED-LOOP OUTER JOIN | |5 |965183 |3 |508020 |0 |0 || |2 |│ ├─NESTED-LOOP OUTER JOIN | |5 |965091 |3 |508020 |0 |0 || |3 |│ │ ├─NESTED-LOOP OUTER JOIN | |5 |965000 |3 |508020 |0 |0 || |4 |│ │ │ ├─NESTED-LOOP OUTER JOIN | |5 |964909 |3 |508020 |0 |0 || |5 |│ │ │ │ ├─NESTED-LOOP OUTER JOIN | |5 |964817 |3 |508020 |0 |0 || |6 |│ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |5 |964726 |3 |508020 |0 |0 || |7 |│ │ │ │ │ │ ├─NESTED-LOOP OUTER JOIN | |5 |964603 |3 |508020 |0 |0 || |8 |│ │ │ │ │ │ │ ├─SUBPLAN SCAN |VIEW1 |5 |964511 |3 |508020 |0 |0 || |9 |│ │ │ │ │ │ │ │ └─LIMIT | |5 |964511 |3 |508020 |0 |0 || |10|│ │ │ │ │ │ │ │ └─TOP-N SORT | |5 |964511 |3 |508020 |0 |0 || |11|│ │ │ │ │ │ │ │ └─NESTED-LOOP OUTER JOIN | |9574 |964203 |3 |505930 |0 |0 || |12|│ │ │ │ │ │ │ │ ├─NESTED-LOOP JOIN | |10336 |775731 |3 |505930 |0 |0 || |13|│ │ │ │ │ │ │ │ │ ├─TABLE RANGE SCAN |L(IDX_LCCONT_SMCCC) |31195 |708 |31195 |505930 |0 |0 || |14|│ │ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN|A(ADMINFILEINFO_CONTNO) |1 |25 |3 |505930 |0 |0 || |15|│ │ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |R |1 |18 |3 |476189 |0 |0 || |16|│ │ │ │ │ │ │ └─DISTRIBUTED TABLE GET |A |1 |18 |3 |508020 |0 |0 || |17|│ │ │ │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |L(LCCONT_INDEX_2) |1 |24 |3 |508020 |0 |0 || |18|│ │ │ │ │ └─DISTRIBUTED TABLE GET |R |1 |18 |3 |508020 |0 |0 || |19|│ │ │ │ └─DISTRIBUTED TABLE GET |C |1 |18 |3 |508020 |0 |0 || |20|│ │ │ └─DISTRIBUTED TABLE GET |LCP |1 |18 |0 |508020 |0 |0 || |21|│ │ └─DISTRIBUTED TABLE GET |LR |1 |18 |3 |508020 |0 |0 || |22|│ └─DISTRIBUTED TABLE RANGE SCAN |RE |1 |18 |0 |508020 |0 |0 || |23|├─TOP-N SORT | |1 |21 |3 |508020 |0 |0 || |24|│ └─DISTRIBUTED TABLE RANGE SCAN |DESIREINFO |1 |21 |3 |508020 |0 |0 || |25|└─DISTRIBUTED TABLE RANGE SCAN |MI_CUSTOMERFEEINFO(IDX_MI_CUSTOMERFEEINFO_CONTNO_MAIN_RISK_PAYDUEDATE,Reverse)|1 |25 |2 |508020 |0 |0 || =================================================================================================================================================================================================================|