SQL Optimization-Beware of error Association

SQL Optimization-Beware of error associations when writing SQL statements, when joining multiple tables, it is sometimes easy to write an error in the association relationship. In general, this problem is easy to find, but if the SQL statement is too long, it is difficult to find it by eyesight alone. I wrote a script today and encountered this problem. The script for the first version is as follows:

[SQL] select detail. commityear, detail. commitmonth, to_char (sysdate-1), 'yyymmm') statmonthid, policy. corppkno, product. prdtsubcatpkno, product. pkno, sum (loss_d.losssum) lossSum_FASH, sum (claim_d.claimsum) claimSum_FASH, sum (distinct) sums, sum (distinct) 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 <span style = "color: rgb (255,102,102);"> detail. pkno = indemnity_d.declaredetailpkno </span> and recovery_d.recoverySumDetail> 0 where product. pkno not in (7, 8, 12, 14, 38) /* Available for shipment */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) /* remove data without loss and other information */group by detail. commityear, detail. commitmonth, policy. corppkno, product. prdtsubcatpkno, product. pkno

 

After the execution, it is found that the data is missing for half a day. In addition, f_t_declaredetail, the table with the largest data volume, contains millions of rows at most. After checking the execution plan, we found that the execution plan is different from the expected one, and the estimated result set is quite large. The execution plan is as follows: as shown in the figure, we can clearly see that table f_t_recoverydetail is embedded with other tables, which is incredible. In addition, the returned result set is more than a million orders of magnitude, it is an order of magnitude larger than f_t_declaredetail. At first, I thought there was a problem with the statistics. I checked the statistics of each table and found no exception. Later, I calmed down and thought that the returned result set would certainly not exceed the data volume of f_t_declaredetail. When f_t_recoverydetail was associated, the data volume crashed to the ground, and I initially suspected it was related. You can look back at the SQL code. The rough font indicates the problem: the association between tables is indeed a problem. Conclusion: Sometimes it is very helpful to see the execution plan without looking at it. Haha