今天幫助兄弟部門優化五險統一征繳資料發送程式，優化其實很簡單，主要是解決了原本不應該執行的全表掃描和笛卡爾積。但問題是為什麼會出現全表掃描和笛卡爾積,是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 decode(c.use_pred_insr,
c.insr_detail_code,
c.use_pred_insr) = c.insr_detail_code
group by a.corp_id,
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品質審計就能避免這種問題的發生。