Welcome to Qi-U Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
572 views
in Technique[技术] by (71.8m points)

oracle group by优化

hcp_pjjg表400W数据
查询耗时3秒左右
image.png

select jg.pjqd_dm,
       (select sjlymc from dm_hcp_sjly where sjly_dm = jg.sjly) sjly,
       jg.pjjg_dm,
       nvl(count(*),0) num
  from hcp_pjjg jg,org_organ org, org_relation_js r
  where r.organcode = jg.swjg_dm
  and r.parentorgancode = org.organcode
  and org.organcode = '14100000000'
  and trunc(jg.pjrq) >= to_date('2020-11-01','yyyy-mm-dd' )
  and trunc(jg.pjrq) <= to_date('2020-11-16','yyyy-mm-dd' )
  and jg.zfbz='N'
 group by jg.pjqd_dm,jg.sjly,jg.pjjg_dm

执行计划
image.png

如果是下面这样, 查询耗时50ms左右
感觉可能是group by造成的

select jg.pjqd_dm,
       jg.sjly,
       jg.pjjg_dm
  from hcp_pjjg jg,org_organ org, org_relation_js r
  where r.organcode = jg.swjg_dm
  and r.parentorgancode = org.organcode
  and org.organcode = '14100000000'
  and trunc(jg.pjrq) >= to_date('2020-11-01','yyyy-mm-dd' )
  and trunc(jg.pjrq) <= to_date('2020-11-16','yyyy-mm-dd' )
  and jg.zfbz='N'

执行计划
image.png

请问该怎么优化??


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

Please log in or register to answer this question.

1 Answer

0 votes
by (71.8m points)
等待大神解答

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Qi-U Community for programmer and developer-Open, Learning and Share

755k questions

547k answers

4 comments

86.4k users

...