oracle优化group,oracle group by优化

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'

执行计划

请问该怎么优化??