? ? 在寫sql時(shí)，在多表關(guān)聯(lián)時(shí)，有時(shí)候容易把關(guān)聯(lián)關(guān)系寫錯(cuò)。一般情況下，該問(wèn)題比較容易發(fā)現(xiàn)，但如果sql較長(zhǎng)時(shí)，光靠眼力就比較難發(fā)現(xiàn)了。今天寫了一個(gè)腳本，碰到該問(wèn)題了。

? ? 第一版本的腳本如下：

?

    select detail.commityear,

       detail.commitmonth,

       to_char((sysdate - 1), 'YYYYMM') statmonthid,

       policy.corppkno,

       product.prdtsubcatpkno,

       product.pkno,

       sum(loss_d.losssum) lossSum_FASH,

       sum(claim_d.claimsum) claimSum_FASH,

       sum(indemnity_d.indemnityRptDetail) indemnityRpt_FASH,

       sum(recovery_d.recoverySumDetail) recoveryRpt_FASH

  from F_T_DeclareDetail detail

  join stdw.d_t_policy policy

    on detail.policypkno = policy.pkno

  join stdw.d_t_producttype product

    on policy.policytypepkno = product.pkno

  left join (select t.declaredetailpkno,

                    sum(nvl(t.losssumdetail, 0)) losssum

               from stdw.f_t_lossdetail t

              group by t.declaredetailpkno) loss_d

    on detail.pkno = loss_d.declaredetailpkno

   and loss_d.losssum > 0

  left join (select claim.declaredetailpkno,

                    sum(nvl(claim.claimsumdetail, 0)) claimsum

               from stdw.F_T_ClaimDetail claim

              group by claim.declaredetailpkno) claim_d

    on detail.pkno = claim_d.declaredetailpkno

   and claim_d.claimsum > 0

  left join (select declareDetailPkNo,

                    sum(nvl(indemnityRptDetail, 0)) indemnityRptDetail

               from stdw.F_T_IndemnityDetail

              group by declareDetailPkNo) indemnity_d

    on detail.pkno = indemnity_d.declaredetailpkno

   and indemnity_d.indemnityRptDetail > 0

  left join (select declaredetailpkno,

                    sum(nvl(recoverySumDetail, 0)) recoverySumDetail

               from stdw.F_T_RecoveryDetail

              group by declaredetailpkno) recovery_d

    on 
    
      detail.pkno = indemnity_d.declaredetailpkno
    
    

   and recovery_d.recoverySumDetail > 0

 where product.pkno not in (7, 8, 12, 14, 38) /*有出運(yùn)*/

   and (loss_d.losssum is not null or claim_d.claimsum is not null or

       indemnity_d.indemnityRptDetail is not null or

       recovery_d.recoverySumDetail is not null) /*剔除沒(méi)有報(bào)損等信息的數(shù)據(jù)*/

 group by detail.commityear,

          detail.commitmonth,

          policy.corppkno,

          product.prdtsubcatpkno,

          product.pkno


  

?

執(zhí)行后，發(fā)現(xiàn)半天沒(méi)出來(lái)數(shù)。而且這些表中，數(shù)據(jù)量最大的表f_t_declaredetail也就幾百萬(wàn)條，在極致情況下，最多返回幾百萬(wàn)行數(shù)據(jù)。查看了下執(zhí)行計(jì)劃，發(fā)現(xiàn)執(zhí)行計(jì)劃和預(yù)計(jì)的不一樣，而且預(yù)估的結(jié)果集相當(dāng)大。執(zhí)行計(jì)劃如下：

? ? 根據(jù)圖示，可以比較清楚的看到，表f_t_recoverydetail居然與其他的表做了內(nèi)嵌循環(huán)關(guān)聯(lián)，不可思議啊，而且返回的結(jié)果集，遠(yuǎn)超百萬(wàn)數(shù)量級(jí)，比f(wàn)_t_declaredetail的數(shù)量級(jí)還大。起初以為是統(tǒng)計(jì)信息出了問(wèn)題，查看了各表的統(tǒng)計(jì)信息，發(fā)現(xiàn)沒(méi)有什么異常。

? ? 后來(lái)靜下來(lái)想了想，返回的結(jié)果集肯定不會(huì)超過(guò)f_t_declaredetail的數(shù)據(jù)量，正好與f_t_recoverydetail關(guān)聯(lián)時(shí)，數(shù)據(jù)量嗖地上去了，初步懷疑是關(guān)聯(lián)的問(wèn)題。可以回頭看下sql代碼，粗字體表明的地方就是問(wèn)題所在：確實(shí)是表之間關(guān)聯(lián)出了問(wèn)題。

? ? 總結(jié)：有時(shí)候肉眼看不出來(lái)，就用執(zhí)行計(jì)劃看吧，還是有很大幫助的。呵呵

?

?

sql優(yōu)化-提防錯(cuò)誤關(guān)聯(lián)