oracle 优化

SELECT rp.thirdserialno,
                bc.serialno,
                bc.businesstype,
                ii.fullname,
                ii.mobiletelephone,
                ii.certid,
                case when substr(bc.PAYACCOUNTNO2,7,5)='88888' then 1 else 2 end as isXCAccountNo,
                bp.putoutdate,
                bp.businesssum,
                rp.paydate,
                to_date((SELECT businessdate FROM system_setup),
                              'yyyy/mm/dd') -
                      to_date(rp.paydate, 'yyyy/mm/dd') as overduedate,
                (rp.payprincipalamt - nvl(bw.actualpayprincipaamt,0) + rp.payinterestamt - nvl(bw.actualpayinterestamt,0) + nvl(bw.actualpayprincipalpenaltyamt,0)) as PAYAMT,
                rp.payprincipalamt - nvl(bw.actualpayprincipaamt,0) as payprincipalamt,
                rp.payinterestamt - nvl(bw.actualpayinterestamt,0) as payinterestamt,
                nvl(bw.actualpayprincipalpenaltyamt,0) as payprincipalpenaltyamt,
                bc.PAYACCOUNTNO2 as PAYACCOUNTNO2,
                XA.ACCOUNTBALANCE AS ACCOUNTBALANCE,
                XA.CHECKDATE as balanceCheckDate
           FROM CG_RETURNPLAN        rp
                LEFT JOIN business_contract BC ON rp.thirdserialno = bc.thirdserialno
                LEFT JOIN business_putout      bp on bp.thirdserialno = rp.thirdserialno
                LEFT JOIN ind_info             ii on bp.customerid = ii.customerid
                LEFT JOIN cg_businesswastebook bw on rp.thirdserialno = bw.thirdserialno and rp.periodno = bw.term
                LEFT JOIN xc_account xa on rp.THIRDSERIALNO = XA.THIRDSERIALNO
          WHERE 
            BC.BUSINESSTYPE IN ('11405040','11404010','11404040')
            and to_date((SELECT businessdate FROM system_setup),
                              'yyyy/mm/dd') -
                      to_date(rp.paydate, 'yyyy/mm/dd') between 1 and 5 --逾期天数小于5
            and instr(rp.thirdserialno,'_2')=0  --非副合同数据
            and ((bw.occurtype in ('0','4') and rp.periodno = bw.term and (bw.actualpayprincipaamt < rp.payprincipalamt or
                bw.actualpayinterestamt < rp.payinterestamt)) or (not exists (SELECT 1 --不存在当期还款流水
                   FROM cg_businesswastebook bwb
                  WHERE rp.thirdserialno = bwb.thirdserialno
                    and rp.periodno = bwb.term)))

如上是问题sql，如下是修改后的sql

SELECT op.thirdserialno,
       op.serialno,
       op.businesstype,
       op.fullname,
       op.mobiletelephone,
       op.certid,
       op.isXCAccountNo,
       op.putoutdate,
       op.businesssum,
       op.paydate,
       op.overduedate,
       op.PAYAMT,
       op.payprincipalamt,
       op.payinterestamt,
       op.payprincipalpenaltyamt,
       op.PAYACCOUNTNO2 as PAYACCOUNTNO2,
       XA.ACCOUNTBALANCE AS ACCOUNTBALANCE,
       XA.CHECKDATE as balanceCheckDate
  FROM (SELECT rpb.thirdserialno,
               bc.serialno,
               bc.businesstype,
               ii.fullname,
               ii.mobiletelephone,
               ii.certid,
               case
                 when substr(bc.PAYACCOUNTNO2, 7, 5) = '88888' then
                  1
                 else
                  2
               end as isXCAccountNo,
               bp.putoutdate,
               bp.businesssum,
               rpb.paydate,
               rpb.overduedate,
               rpb.PAYAMT,
               rpb.payprincipalamt,
               rpb.payinterestamt,
               rpb.payprincipalpenaltyamt,
               bc.PAYACCOUNTNO2 as PAYACCOUNTNO2
          FROM (SELECT rp.thirdserialno,
                       rp.paydate,
                       to_date((SELECT businessdate FROM system_setup),
                              'yyyy/mm/dd') -
                      to_date(rp.paydate, 'yyyy/mm/dd') as overduedate,
                       (rp.payprincipalamt - nvl(bw.actualpayprincipaamt, 0) +
                       rp.payinterestamt - nvl(bw.actualpayinterestamt, 0) +
                       nvl(bw.actualpayprincipalpenaltyamt, 0)) as PAYAMT,
                       rp.payprincipalamt - nvl(bw.actualpayprincipaamt, 0) as payprincipalamt,
                       rp.payinterestamt - nvl(bw.actualpayinterestamt, 0) as payinterestamt,
                       nvl(bw.actualpayprincipalpenaltyamt, 0) as payprincipalpenaltyamt
                  FROM (SELECT *
                          FROM CG_RETURNPLAN
                         WHERE paydate <
                               (SELECT businessdate FROM system_setup)
                           AND paydate > to_char(to_date((SELECT businessdate
                                                           FROM system_setup),
                                                         'yyyy/mm/dd') - 6,
                                                 'yyyy/mm/dd')
                           and instr(thirdserialno, '_2') = 0) rp
                  left join cg_businesswastebook bw
                    on rp.thirdserialno = bw.thirdserialno
                   and rp.periodno = bw.term
                   and ((bw.occurtype in ('0', '4') and rp.periodno = bw.term and
                       (bw.actualpayprincipaamt < rp.payprincipalamt or
                       bw.actualpayinterestamt < rp.payinterestamt)) or
                       (not exists
                        (SELECT 1 --不存在当期还款流水
                            FROM cg_businesswastebook bwb
                           WHERE rp.thirdserialno = bwb.thirdserialno
                             and rp.periodno = bwb.term)))) rpb,
               business_contract bc,
               business_putout bp,
               ind_info ii
         WHERE rpb.thirdserialno = bc.thirdserialno
           and rpb.thirdserialno = bp.thirdserialno
           and bp.customerid = ii.customerid) op
  LEFT JOIN xc_account xa
    on op.THIRDSERIALNO = XA.THIRDSERIALNO;

修改点：

修改了日期的过滤条件，不在where里用函数进行处理，会特别慢

重新梳理表与表之间的逻辑关系，用准确的内，左右连接进行关联，避免出现3张表以上的表进行全关联

收获：

oracle有执行计划，查看执行计划可以知道哪些sql是消耗时间比较大的，可以针对进行处理。执行计划可以着重研究一下

自己有oracle视频，回去需要学习一下。