sql语句调优
如题：具体sql为：insert into plcy_car_evt_mdt
select a.plcy_cd,a.rl_dt_id,
a.start_dt_id,a.org_cd,trim(a.prod_cd)||b.cvr_kind_cd,a.cust_grp,
a.clt_cd,a.chnl_tp_cd,a. plcy_seg_id,a.rnew_ind,b.cur_cd,a.ci_ind,
case whena.plcy_otflg<>'N'
then b.plcy_prem else 0 end plcy_prem,
case whena.plcy_otflg<>'N'
then b.nfee_prem else 0 end nfee_prem,
b.plcy_dsct_amt plcy_dsct_amt,
b.plcy_xy_amt plcy_xy_amt,
d.plcy_std_prem plcy_std_prem,
b.sign_std_prem sign_std_prem,
case when a.plcy_otflg<>'N'
thenb.plcy_amnt else 0 end plcy_amnt,
b.plcy_cnt,a.load_dt_id,a.upt_dt_id
from plcy_car_cnt a join plcy_car_evt_dt b on a.plcy_cd=b.plcy_cd
left join plcy_car_evt_ori_dt d on b.plcy_cd=d.plcy_cd
and b.cvr_kind_cd=d.cvr_kind_cd and b.cur_cd=d.cur_cd
where a.plcy_cd not in( --业务的需要必须not in
select i.plcy_cd from plcy_car_endr_tmp i where i.efct_dt_id<=a.start_dt_id)
在相应的列上已经创建了索引，那位大老过来帮看看是否还有优化的余地
令上述语句的执行计划为：
----------------------------------------------------------------------------------------------------------------------------
Estimated Cost: 125926304
Estimated # of Rows Returned: 107742784
1) dm.b: SEQUENTIAL SCAN(Serial, fragments: ALL)
2) dm.a: INDEX PATH
Filters: dm.a.plcy_cd != ALL <subquery>
(1) Index Keys: plcy_cd (Serial, fragments: ALL)
Lower Index Filter: dm.a.plcy_cd = dm.b.plcy_cd
ON-Filters:dm.a.plcy_cd = dm.b.plcy_cd
NESTED LOOP JOIN
3) dm.d: INDEX PATH
(1) Index Keys: plcy_cd cvr_kind_cd cur_cd (Serial, fragments: ALL)
Lower Index Filter: ((dm.b.plcy_cd = dm.d.plcy_cd AND dm.b.cvr_kind_cd = dm.d.cvr_kind_cd ) AND dm.b.cur_cd = dm.d.cur_cd )
ON-Filters:((dm.b.plcy_cd = dm.d.plcy_cd AND dm.b.cvr_kind_cd = dm.d.cvr_kind_cd ) AND dm.b.cur_cd = dm.d.cur_cd )
NESTED LOOP JOIN(LEFT OUTER JOIN)
PostJoin-Filters:dm.a.plcy_cd != ALL <subquery>
Subquery:
---------
Estimated Cost: 4
Estimated # of Rows Returned: 11
1) dm.i: INDEX PATH
(1) Index Keys: efct_dt_id (Serial, fragments: ALL)
Upper Index Filter: dm.i.efct_dt_id <= dm.a.start_dt_id not in 应该不是用索引的
另外连接的时候其中一个表全表扫描来和另一个表连接，这里全扫的b表，如果a表的数据量小，可以将a，b位置换一下看看有没有变化。 偶知道not in是不走索引。貌似not exists比not in快（不知道informix是不是？）。还有就是b的数据更多些，明天调整下连接顺序，把not in换成not exists再测试下~最后谢谢您的指点！哈哈^_^ 哦也！按以上思路，速度快了很多!
------------------------------只调整表的连接顺序，速度基本没什么提高--------------------------------------------------------------------------------------
QUERY:
------
insert into plcy_car_evt_mdt
select a.plcy_cd,a.rl_dt_id,
a.start_dt_id,a.org_cd,trim(a.prod_cd)||b.cvr_kind_cd,a.cust_grp,
a.clt_cd,a.chnl_tp_cd,a. plcy_seg_id,a.rnew_ind,b.cur_cd,a.ci_ind,
case whena.plcy_otflg<>'N'
then b.plcy_prem else 0 end plcy_prem,
case whena.plcy_otflg<>'N'
then b.nfee_prem else 0 end nfee_prem,
b.plcy_dsct_amt plcy_dsct_amt,
b.plcy_xy_amt plcy_xy_amt,
d.plcy_std_prem plcy_std_prem,
b.sign_std_prem sign_std_prem,
case when a.plcy_otflg<>'N'
thenb.plcy_amnt else 0 end plcy_amnt,
b.plcy_cnt,a.load_dt_id,a.upt_dt_id
from plcy_car_evt_dt b join plcy_car_cnt a on a.plcy_cd=b.plcy_cd
left join plcy_car_evt_ori_dt d on b.plcy_cd=d.plcy_cd
and b.cvr_kind_cd=d.cvr_kind_cd and b.cur_cd=d.cur_cd
where a.plcy_cd not in(
select i.plcy_cd from plcy_car_endr_tmp i where i.efct_dt_id<=a.start_dt_id)
Estimated Cost: 125926304
Estimated # of Rows Returned: 107742784
1) dm.b: SEQUENTIAL SCAN(Serial, fragments: ALL)
2) dm.a: INDEX PATH
Filters: dm.a.plcy_cd != ALL <subquery>
(1) Index Keys: plcy_cd (Serial, fragments: ALL)
Lower Index Filter: dm.a.plcy_cd = dm.b.plcy_cd
ON-Filters:dm.a.plcy_cd = dm.b.plcy_cd
NESTED LOOP JOIN
3) dm.d: INDEX PATH
(1) Index Keys: plcy_cd cvr_kind_cd cur_cd (Serial, fragments: ALL)
Lower Index Filter: ((dm.b.plcy_cd = dm.d.plcy_cd AND dm.b.cvr_kind_cd = dm.d.cvr_kind_cd ) AND dm.b.cur_cd = dm.d.cur_cd )
ON-Filters:((dm.b.plcy_cd = dm.d.plcy_cd AND dm.b.cvr_kind_cd = dm.d.cvr_kind_cd ) AND dm.b.cur_cd = dm.d.cur_cd )
NESTED LOOP JOIN(LEFT OUTER JOIN)
PostJoin-Filters:dm.a.plcy_cd != ALL <subquery>
Subquery:
---------
Estimated Cost: 4
Estimated # of Rows Returned: 11
1) dm.i: INDEX PATH
(1) Index Keys: efct_dt_id (Serial, fragments: ALL)
Upper Index Filter: dm.i.efct_dt_id <= dm.a.start_dt_id
---------------------------------------------------not in换成not exists后--------------------------------------------------------------
insert into plcy_car_evt_mdt
select a.plcy_cd,a.rl_dt_id,
a.start_dt_id,a.org_cd,trim(a.prod_cd)||b.cvr_kind_cd,a.cust_grp,
a.clt_cd,a.chnl_tp_cd,a. plcy_seg_id,a.rnew_ind,b.cur_cd,a.ci_ind,
case whena.plcy_otflg<>'N'
then b.plcy_prem else 0 end plcy_prem,
case whena.plcy_otflg<>'N'
then b.nfee_prem else 0 end nfee_prem,
b.plcy_dsct_amt plcy_dsct_amt,
b.plcy_xy_amt plcy_xy_amt,
d.plcy_std_prem plcy_std_prem,
b.sign_std_prem sign_std_prem,
case when a.plcy_otflg<>'N'
thenb.plcy_amnt else 0 end plcy_amnt,
b.plcy_cnt,a.load_dt_id,a.upt_dt_id
from plcy_car_evt_dt b join plcy_car_cnt a on a.plcy_cd=b.plcy_cd
left join plcy_car_evt_ori_dt d on b.plcy_cd=d.plcy_cd
and b.cvr_kind_cd=d.cvr_kind_cd and b.cur_cd=d.cur_cd
where not exists(
select * from plcy_car_endr_tmp i where i.plcy_cd=a.plcy_cd and i.efct_dt_id<=a.start_dt_id)
Estimated Cost: 63804204
Estimated # of Rows Returned: 54162628
1) dm.a: SEQUENTIAL SCAN(Serial, fragments: ALL)
Filters: NOT EXISTS <subquery>
2) dm.b: AUTOINDEX PATH
(1) Index Keys: plcy_cd (Serial, fragments: ALL)
Lower Index Filter: dm.a.plcy_cd = dm.b.plcy_cd
ON-Filters:dm.a.plcy_cd = dm.b.plcy_cd
NESTED LOOP JOIN
3) dm.d: INDEX PATH
(1) Index Keys: plcy_cd cvr_kind_cd cur_cd (Serial, fragments: ALL)
Lower Index Filter: ((dm.b.plcy_cd = dm.d.plcy_cd AND dm.b.cvr_kind_cd = dm.d.cvr_kind_cd ) AND dm.b.cur_cd = dm.d.cur_cd )
ON-Filters:((dm.b.plcy_cd = dm.d.plcy_cd AND dm.b.cvr_kind_cd = dm.d.cvr_kind_cd ) AND dm.b.cur_cd = dm.d.cur_cd )
NESTED LOOP JOIN(LEFT OUTER JOIN)
PostJoin-Filters:NOT EXISTS <subquery>
Subquery:
---------
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) dm.i: INDEX PATH
Filters: dm.i.plcy_cd = dm.a.plcy_cd
(1) Index Keys: efct_dt_id (Serial, fragments: ALL)
Upper Index Filter: dm.i.efct_dt_id <= dm.a.start_dt_id 为什么要not in，外连接不行吗？我一直以为外连接是很牛X的
方法是使用外连接，然后判断外连接的键值是空的就可以得到你要的数据 我不知道not exists是否是按照外连接来优化的，但我知道外连接是肯定比not in快的，有过实战经验 请问用外连接怎么改写。 谢谢ls两位兄弟的关注，外连接的速度应该比Not in快的，而之所以不使用外连接是因为受到业务的限制，使用外连接会关联出主表多条记录。
页:
[1]