將SQL質量審計引入軟體開發可以避免不必要的SQL優化工作

今天幫助兄弟部門優化五險統一徵繳資料傳送程式，優化其實很簡單，主要是解決了原本不應該執行的全表掃描和笛卡爾積。但問題是為什麼會出現全表掃描和笛卡爾積,是Oracle優化器選擇錯了執行計劃嗎，答案並不是，原因就是在設計表結構時的缺陷造成的，如果在設計表結構時能夠根據業務合理設計，也就沒有這次優化了。其實這個問題我在公司就提過，但不重視，現在我成了甲方，我又要當救火隊員了。

下面是每個月社會保障系統向五險徵繳系統傳送每月所有單位各個險種的應繳資料的查詢語句:
Select t.Pay_Object_Id,
       t.Pay_Object_Code,
       t.Pay_Object_Name,
       t.Insr_Detail_Code,
       t.asgn_tenet,
       t.asgn_order,
       t.use_pred_insr,
       Sum(t.Topay_Money) as topay_money,
       Sum(Pay_Money) as pay_money,
       Sum(Pred_Money) as pred_money,
       to_char(sysdate, 'yyyy-mm-dd') as pay_time,
       t.corp_type_code
  From (Select T1.Corp_Id As Pay_Object_Id,
               T1.Insr_Detail_Code,
               T1.Corp_Code As Pay_Object_Code,
               T1.Corp_Name As Pay_Object_Name,
               T1.asgn_tenet,
               T1.asgn_order,
               T1.use_pred_insr,
               Decode(Sign(T1.pay_Money),
                      -1,
                      T1.pay_Money,
                      Decode(Sign(T1.pay_Money -
                                  Decode(Sign(T1.pay_Money),
                                         -1,
                                         0,
                                         Nvl(T2.Pred_Money, 0))),
                             -1,
                             0,
                             T1.pay_Money -
                             Decode(Sign(T1.pay_Money),
                                    -1,
                                    0,
                                    Nvl(T2.Pred_Money, 0)))) As pay_Money,
               T1.toPay_Money,
               Nvl(T2.Pred_Money, 0) As Pred_Money,
               T1.corp_type_code
          from (select t11.Corp_Id,
                       t11.Corp_Code,
                       t11.Corp_Name,
                       t11.Insr_Detail_Code,
                       sum(t11.Topay_Money) as Topay_Money,
                       t11.corp_type_code,
                       sum(t11.Pay_Money) as Pay_Money,
                       t11.asgn_tenet,
                       t11.asgn_order,
                       t11.use_pred_insr
                  from (Select b.Corp_Id,
                               a.Corp_Code,
                               a.Corp_Name,
                               b.insr_detail_code,
                               a.corp_type_code,
                               Sum(b.Pay_Money - nvl(b.Payed_Money, 0)) As Topay_Money,
                               Sum(b.Pay_Money - nvl(b.Payed_Money, 0)) As Pay_Money,
                               c.asgn_tenet,
                               c.asgn_order,
                               c.use_pred_insr
                          From Bs_Corp a, Lv_Insr_Topay b, lv_scheme_detail c
                         Where a.Corp_Id = b. Corp_Id
                           and ((b.payed_flag = 0 and
                               nvl(b.busi_asg_no, 0) = 0) or
                               (b.payed_flag = 2))
                           and nvl(b.indi_pay_flag, 0) = 0
                           and c.scheme_id = 1
                           and b.insr_detail_code=c.insr_detail_code
                           and not exists
                         (select 'x'
                                  from lv_busi_bill lbb, lv_busi_record lbr
                                 where b.corp_id = lbr.pay_object_id
                                   and lbb.busi_bill_sn = lbr.busi_bill_sn
                                   and lbb.pay_object = 1
                                   and lbb.audit_flag = 0)
                           and c.insr_detail_code = b.insr_detail_code
                           and b.calc_prd < = '201508'
                           and b.insr_detail_code in
                               (select distinct insr_detail_code
                                  from lv_scheme_detail
                                 where scheme_id = 1)
                           and b.topay_type in
                               (select topay_type
                                  from lv_busi_type_topay
                                 where busi_type = 1)
                           and b.src_type = 1
                           and a.center_id = '430701'
                         Group By b.Corp_Id,
                                  b.Insr_Detail_Code,
                                  c.use_pred_insr,
                                  a.Corp_Code,
                                  a.Corp_Name,
                                  a.corp_type_code,
                                  c.asgn_tenet,
                                  c.asgn_order,
                                  c.use_pred_insr) t11
                 group by t11.Corp_Id,
                          t11.Corp_Code,
                          t11.Corp_Name,
                          t11.Insr_Detail_Code,
                          t11.corp_type_code,
                          t11.asgn_tenet,
                          t11.asgn_order,
                          t11.use_pred_insr) T1,
               (select t21.corp_id,
                       sum(t21.pred_money) as pred_money,
                       t21.Insr_Detail_Code
                  from (Select a.Corp_Id,
                               decode(c.use_pred_insr,
                                      null,
                                      b.insr_detail_code,
                                      c.use_pred_insr) as Insr_Detail_Code,
                               sum(decode(1, 0, 0, 1, b.Pred_Money)) as pred_money
                          From Bs_Corp a, Lv_Pred_Money b, lv_scheme_detail c
                         Where a.Corp_Id = b.Corp_Id
                           and c.insr_detail_code = b.insr_detail_code
                           and c.scheme_id = 1
                           and decode(c.use_pred_insr,
                                      null,
                                      c.insr_detail_code,
                                      c.use_pred_insr) = c.insr_detail_code
                         group by a.corp_id,
                                  c.use_pred_insr,
                                  b.insr_detail_code) t21
                 group by t21.corp_id, t21.Insr_Detail_Code) T2
         Where T1.Corp_Id = T2.Corp_Id(+)
           And T1.Insr_Detail_Code = T2.Insr_Detail_Code(+)) t
where not exists (select 'X'
          from lv_busi_bill a, lv_busi_record b
         where a.busi_bill_sn = b.busi_bill_sn
           and a.audit_flag = 0
           and a.pay_object = 1
           and b.PAY_OBJECT_ID = t.PAY_OBJECT_ID
           and b.INSR_DETAIL_CODE = t.insr_detail_code)
Group By t.pay_money,
          t.Pay_Object_Id,
          t.Pay_Object_Code,
          t.Pay_Object_Name,
          t.corp_type_code,
          t.insr_detail_code,
          t.asgn_tenet,
          t.asgn_order,
          t.use_pred_insr
Having Sum(t.pay_Money) = 0
order by t.Pay_Object_Name, t.asgn_order

其執行計劃的統計資訊如下:

執行時間是1481秒，這個時間是不可接受的。

其執行計劃如下:

執行計劃中對錶lv_busi_record執行全表掃描，該表記錄有2000w,這明顯是不對，為什麼不走索引了，是因為表在設計和建立時就沒有建立索引，這個表的資料是不斷增加的，前期資料量少，執行全表掃描對效能的影響就根本體現不出來，但隨著系統的執行，資料量的增加就會越來越慢。還有就是表lv_scheme_detail和Bs_Corp之間的笛卡爾積，為什麼會出現笛卡爾積了，發現兩個表之間根本就沒有關聯條件，一開始還以為開發人員忘記書寫了，但經過查詢表空間發現，兩個表根本就沒有可以關聯的欄位，而最後使用了group by來進行去重。

這裡我只能對錶lv_busi_record根據業務規則建立索引，但沒有辦法解決表lv_scheme_detail和Bs_Corp之間的笛卡爾積關聯的問題
如果修改表結構就涉及到修改應用程式了。在對錶lv_busi_record索引後的執行情況如下。
其執行計劃的統計資訊如下:

執行時間縮短為接近14秒，從1481到14是百倍的提升。其實處理方法很簡單，但我想說的是，這本就不應該出現的，如果我們軟體開發商在設計，開發和測試階段能認真設計，編寫SQL和測試，也就是引入SQL質量審計就能避免這種問題的發生。

Select t.Pay_Object_Id,
       t.Pay_Object_Code,
       t.Pay_Object_Name,
       t.Insr_Detail_Code,
       t.asgn_tenet,
       t.asgn_order,
       t.use_pred_insr,
       Sum(t.Topay_Money) as topay_money,
       Sum(Pay_Money) as pay_money,
       Sum(Pred_Money) as pred_money,
       to_char(sysdate, 'yyyy-mm-dd') as pay_time,
       t.corp_type_code
  From (Select T1.Corp_Id As Pay_Object_Id,
               T1.Insr_Detail_Code,
               T1.Corp_Code As Pay_Object_Code,
               T1.Corp_Name As Pay_Object_Name,
               T1.asgn_tenet,
               T1.asgn_order,
               T1.use_pred_insr,
               Decode(Sign(T1.pay_Money),
                      -1,
                      T1.pay_Money,
                      Decode(Sign(T1.pay_Money -
                                  Decode(Sign(T1.pay_Money),
                                         -1,
                                         0,
                                         Nvl(T2.Pred_Money, 0))),
                             -1,
                             0,
                             T1.pay_Money -
                             Decode(Sign(T1.pay_Money),
                                    -1,
                                    0,
                                    Nvl(T2.Pred_Money, 0)))) As pay_Money,
               T1.toPay_Money,
               Nvl(T2.Pred_Money, 0) As Pred_Money,
               T1.corp_type_code
          from (select t11.Corp_Id,
                       t11.Corp_Code,
                       t11.Corp_Name,
                       t11.Insr_Detail_Code,
                       sum(t11.Topay_Money) as Topay_Money,
                       t11.corp_type_code,
                       sum(t11.Pay_Money) as Pay_Money,
                       t11.asgn_tenet,
                       t11.asgn_order,
                       t11.use_pred_insr
                  from (Select b.Corp_Id,
                               a.Corp_Code,
                               a.Corp_Name,
                               b.insr_detail_code,
                               a.corp_type_code,
                               Sum(b.Pay_Money - nvl(b.Payed_Money, 0)) As Topay_Money,
                               Sum(b.Pay_Money - nvl(b.Payed_Money, 0)) As Pay_Money,
                               c.asgn_tenet,
                               c.asgn_order,
                               c.use_pred_insr
                          From Bs_Corp a, Lv_Insr_Topay b, lv_scheme_detail c
                         Where a.Corp_Id = b. Corp_Id
                           and ((b.payed_flag = 0 and
                               nvl(b.busi_asg_no, 0) = 0) or
                               (b.payed_flag = 2))
                           and nvl(b.indi_pay_flag, 0) = 0
                           and c.scheme_id = 1
                           and b.insr_detail_code=c.insr_detail_code
                           and not exists
                         (select 'x'
                                  from lv_busi_bill lbb, lv_busi_record lbr
                                 where b.corp_id = lbr.pay_object_id
                                   and lbb.busi_bill_sn = lbr.busi_bill_sn
                                   and lbb.pay_object = 1
                                   and lbb.audit_flag = 0)
                           and c.insr_detail_code = b.insr_detail_code
                           and b.calc_prd < = '201508'
                           and b.insr_detail_code in
                               (select distinct insr_detail_code
                                  from lv_scheme_detail
                                 where scheme_id = 1)
                           and b.topay_type in
                               (select topay_type
                                  from lv_busi_type_topay
                                 where busi_type = 1)
                           and b.src_type = 1
                           and a.center_id = '430701'
                         Group By b.Corp_Id,
                                  b.Insr_Detail_Code,
                                  c.use_pred_insr,
                                  a.Corp_Code,
                                  a.Corp_Name,
                                  a.corp_type_code,
                                  c.asgn_tenet,
                                  c.asgn_order,
                                  c.use_pred_insr) t11
                 group by t11.Corp_Id,
                          t11.Corp_Code,
                          t11.Corp_Name,
                          t11.Insr_Detail_Code,
                          t11.corp_type_code,
                          t11.asgn_tenet,
                          t11.asgn_order,
                          t11.use_pred_insr) T1,
               (select t21.corp_id,
                       sum(t21.pred_money) as pred_money,
                       t21.Insr_Detail_Code
                  from (Select a.Corp_Id,
                               decode(c.use_pred_insr,
                                      null,
                                      b.insr_detail_code,
                                      c.use_pred_insr) as Insr_Detail_Code,
                               sum(decode(1, 0, 0, 1, b.Pred_Money)) as pred_money
                          From Bs_Corp a, Lv_Pred_Money b, lv_scheme_detail c
                         Where a.Corp_Id = b.Corp_Id
                           and c.insr_detail_code = b.insr_detail_code
                           and c.scheme_id = 1
                           and decode(c.use_pred_insr,
                                      null,
                                      c.insr_detail_code,
                                      c.use_pred_insr) = c.insr_detail_code
                         group by a.corp_id,
                                  c.use_pred_insr,
                                  b.insr_detail_code) t21
                 group by t21.corp_id, t21.Insr_Detail_Code) T2
         Where T1.Corp_Id = T2.Corp_Id(+)
           And T1.Insr_Detail_Code = T2.Insr_Detail_Code(+)) t
 where not exists (select 'X'
          from lv_busi_bill a, lv_busi_record b
         where a.busi_bill_sn = b.busi_bill_sn
           and a.audit_flag = 0
           and a.pay_object = 1
           and b.PAY_OBJECT_ID = t.PAY_OBJECT_ID
           and b.INSR_DETAIL_CODE = t.insr_detail_code)
 Group By t.pay_money,
          t.Pay_Object_Id,
          t.Pay_Object_Code,
          t.Pay_Object_Name,
          t.corp_type_code,
          t.insr_detail_code,
          t.asgn_tenet,
          t.asgn_order,
          t.use_pred_insr
Having Sum(t.pay_Money) = 0
 order by t.Pay_Object_Name, t.asgn_order
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-1770592/，如需轉載，請註明出處，否則將追究法律責任。