oracle进行sql优化

原创于 2019-08-02 13:59:35 发布 · 289 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracl

oracle 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了在Oracle数据库中对SQL查询进行优化的过程。在优化前后对比中，虽然资源消耗未见显著减少，但查询速度从原先的长时间延迟提升到了4秒左右，显著提高了效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

优化前

   select sqdbh,sqr,bm,sqrq,skdwbm,skdwmc,sqfkje ,'N' as xz_flag  ,sjfkrq,fkdwkhyh
   from yw_fksqd 
   INNER JOIN rs_department on bm = d_no
   left join  CW_ZNHS_RK on instr(fksqh,sqdbh) > 0 AND  (CW_ZNHS_RK.zt_no = 'JG')
   where rs_department.zt_no = 'JG' 
   and ((rs_department.tt_no in ('29','32','34') and (state = '付款' or state = '已审核' or state = '批准' OR STATE = 'pz'))) 
   and fksqh is null  
   and to_char(sqrq,'yyyy/mm') >= '2018/12'

在这里插入图片描述
优化后


   select sqdbh,sqr,bm,sqrq,skdwbm,skdwmc,sqfkje ,'N' as xz_flag  ,sjfkrq,fkdwkhyh
   from (select * from yw_fksqd where  to_char(sqrq,'yyyy/mm') >= '2018/12' and state in ('付款' , '已审核' , '批准', 'pz'))
   INNER JOIN (select d_no from rs_department where zt_no = :as_zt and tt_no in ('29','32','34')) on bm = d_no
   left join  (select fksqh from CW_ZNHS_RK where zt_no = :as_zt )  on instr(fksqh,sqdbh) > 0 
   where fksqh is null