Query takes about 3 seconds
The select jg pjqd_dm,
(select sjlymc from dm_hcp_sjly where sjly_dm=jg. Sjly) sjly,
Jg pjjg_dm,
NVL (count (*), 0) num
The from hcp_pjjg jg, org_organ org, org_relation_js r
Where r.org ancode=jg. Swjg_dm
And r.p arentorgancode=org.org ancode
And org.org ancode='14100000000'
And trunc (jg PJRQ) & gt;=to_date (' 2020-11-01 ', '- dd yyyy - mm)
And trunc (jg PJRQ) & lt;=to_date (' 2020-11-16 ', '- dd yyyy - mm)
And jg. ZFBZ='N'
Group by jg pjqd_dm, jg sjly, jg. Pjjg_dm
If it is like this, query takes about 50 ms
Feeling could be the result of a group by
The select jg pjqd_dm,
Jg sjly,
Jg. Pjjg_dm
The from hcp_pjjg jg, org_organ org, org_relation_js r
Where r.org ancode=jg. Swjg_dm
And r.p arentorgancode=org.org ancode
And org.org ancode='14100000000'
And trunc (jg PJRQ) & gt;=to_date (' 2020-11-01 ', '- dd yyyy - mm)
And trunc (jg PJRQ) & lt;=to_date (' 2020-11-16 ', '- dd yyyy - mm)
And jg. ZFBZ='N'
How to optimize??
CodePudding user response:
CodePudding user response:
The execution plan
CodePudding user response:
1, in not group by the previous statement, 3 seconds only show the first page data, or shows all of the data line?2, and trunc (jg. PJRQ) & gt;=to_date (' 2020-11-01 ', '- dd yyyy - mm) and trunc (jg. PJRQ) & lt;=to_date (' 2020-11-16 ', '- dd yyyy - mm)
This statement, get rid of the logic of computed columns,
3, build the two tables full complement other post, the index is also posted,