oracle group by优化

Question

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

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

执行计划

如果是下面这样, 查询耗时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'

执行计划

请问该怎么优化??

Answer 1

等待大神解答