关于 where条件下加了子查询 导致速度巨慢的解决方案

问题源自一个sql代码

select 
  count(1) "rwsl",
  count(decode(xx.FPSJ,null,null,1)) "fpsl",
  count(decode(xx.HSSJ,null,null,1)) "fksl",
  count(decode(mx.NEXT_CLHJ,'HSJGCH',1,null)) "hsjgch",
  count(decode(mx.NEXT_CLHJ,'HSJGFH',1,null)) "hsjgfh",
  count(decode(mx.NEXT_CLHJ,'FKJGCH',1,null)) "fkjgch",
  count(decode(mx.NEXT_CLHJ,'FKJGFH',1,null)) "fkjgfh",
  count(decode(mx.NEXT_CLHJ,'FKJGQR',1,null)) "fkjgqr",
  count(decode(mx.NEXT_CLHJ||'!'||mx.LAST_CLZT_DM,'FKJGSP!YQRFKJG',1,null)) "fkjgsp",
  count(decode(mx.xfzt||'!'||mx.LAST_CLZT_DM,'YXF!QRBJS',1,
    'YXF!RWJS',1,'YXF!ECHS',1,null)) "fkspjs",
  count(decode(mx.LAST_CLZT_DM,'QRBJS',1,null)) "qrbjs",
  count(decode(mx.xfzt||'!'||mx.LAST_CLZT_DM,'YXF!RWJS',1,null)) "rwjs",
  count(decode(mx.LAST_CLZT_DM,'ECHS',1,null)) "thhs"
  from  FXYD_YDMX mx,fxyd_rwpc pc,fxyd_clxx xx,dim_swjg swjg 
where mx.ydjg_dm = swjg.swjg_dm
  and swjg.SWJG_DSSWJG_DM = '23403000000'
  and mx.tzs_bh = pc.tzs_bh 
  and mx.fxmx_bh = xx.fxmx_bh(+)
  and pc.TJBZ = 'Y'
--注意下面就是慢的罪魁祸首
              and ((select count(1) from FXYD_RWHDJL h where h.clhj='FKJGSP' and h.zt_clh = 'ECHS' and h.fxmx_bh = mx.fxmx_bh) >= 0) 

 由于where条件后面加了一个子查询 还用到了 聚合函数count 以及数据的比较

导致了oracle将执行计划 强制走了 主表的全表扫描 从而导致执行时间查过了40秒

 

解决方案是 改成

--用历史表先处理好数据 在跟主表去关联过滤
with 
hdtemp as (select h.fxmx_bh  from FXYD_RWHDJL h group by h.fxmx_bh having(count(decode(h.clhj||'#'||h.zt_clh,'FKJGSP#ECHS',1,null)))>=0) 
select 
  count(1) "rwsl",
  count(decode(xx.FPSJ,null,null,1)) "fpsl",
  count(decode(xx.HSSJ,null,null,1)) "fksl",
  count(decode(mx.NEXT_CLHJ,'HSJGCH',1,null)) "hsjgch",
  count(decode(mx.NEXT_CLHJ,'HSJGFH',1,null)) "hsjgfh",
  count(decode(mx.NEXT_CLHJ,'FKJGCH',1,null)) "fkjgch",
  count(decode(mx.NEXT_CLHJ,'FKJGFH',1,null)) "fkjgfh",
  count(decode(mx.NEXT_CLHJ,'FKJGQR',1,null)) "fkjgqr",
  count(decode(mx.NEXT_CLHJ||'!'||mx.LAST_CLZT_DM,'FKJGSP!YQRFKJG',1,null)) "fkjgsp",
  count(decode(mx.xfzt||'!'||mx.LAST_CLZT_DM,'YXF!QRBJS',1,
    'YXF!RWJS',1,'YXF!ECHS',1,null)) "fkspjs",
  count(decode(mx.LAST_CLZT_DM,'QRBJS',1,null)) "qrbjs",
  count(decode(mx.xfzt||'!'||mx.LAST_CLZT_DM,'YXF!RWJS',1,null)) "rwjs",
  count(decode(mx.LAST_CLZT_DM,'ECHS',1,null)) "thhs"
  from  FXYD_YDMX mx,fxyd_rwpc pc,fxyd_clxx xx,dim_swjg swjg,hdtemp 
where mx.ydjg_dm = swjg.swjg_dm
  and swjg.SWJG_DSSWJG_DM = '23403000000'
  and hdtemp.FXMX_BH = mx.fxmx_bh
  and mx.tzs_bh = pc.tzs_bh 
  and mx.fxmx_bh = xx.fxmx_bh(+)
  and pc.TJBZ = 'Y'