今天帮助兄弟部门优化五险统一征缴数据发送程序，优化其实很简单，主要是解决了原本不应该执行的全表扫描和笛卡尔积。但问题是为什么会出现全表扫描和笛卡尔积,是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质量审计就能避免这种问题的发生。