sql语句调优

hero--008

如题：具体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 when  a.plcy_otflg[7]<>'N'
    then b.plcy_prem else 0 end plcy_prem,
  case when  a.plcy_otflg[7]<>'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[7]<>'N'
    then  b.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-Filtersdm.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

wuicpp

not in 应该不是用索引的
另外连接的时候其中一个表全表扫描来和另一个表连接，这里全扫的b表，如果a表的数据量小，可以将a，b位置换一下看看有没有变化。

hero--008

偶知道not in是不走索引。貌似not exists比not in快（不知道informix是不是？）。还有就是b的数据更多些，明天调整下连接顺序，把not in换成not exists再测试下~最后谢谢您的指点！哈哈^_^

hero--008

哦也！按以上思路，速度快了很多!
------------------------------只调整表的连接顺序，速度基本没什么提高--------------------------------------------------------------------------------------
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 when  a.plcy_otflg[7]<>'N'
  then b.plcy_prem else 0 end plcy_prem,
case when  a.plcy_otflg[7]<>'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[7]<>'N'
  then  b.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-Filtersdm.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 when  a.plcy_otflg[7]<>'N'
  then b.plcy_prem else 0 end plcy_prem,
case when  a.plcy_otflg[7]<>'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[7]<>'N'
  then  b.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-Filtersdm.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

blackuhlan

为什么要not in，外连接不行吗？我一直以为外连接是很牛X的
方法是使用外连接，然后判断外连接的键值是空的就可以得到你要的数据

blackuhlan

我不知道not exists是否是按照外连接来优化的，但我知道外连接是肯定比not in快的，有过实战经验

wuicpp

请问用外连接怎么改写。

hero--008

谢谢ls两位兄弟的关注，外连接的速度应该比Not in快的，而之所以不使用外连接是因为受到业务的限制，使用外连接会关联出主表多条记录。