提供的信息太少, 简单优化建议 1、第一个in子查询中的order by 排序是多余的。可以去掉。 2、对于数据量较大的情况,尽量使用表连接方式取代子查询方式。 3、注意过滤条件上的索引的使用。
SELECT aa.workitem_id, aa.formset_inst_id, bb.receive_time, aa.complete_time FROM ( SELECT a.workitem_id, a.formset_inst_id, a.complete_time FROM mv_workitem a WHERE workitem_id IN ( SELECT MAX (t.workitem_id) FROM mv_workitem t WHERE t.formset_id = 347 AND t.receiver_dept = '总裁办公室(股份)' AND t.app_type_name = '部门阅办文件' ) ) aa, (SELECT a.workitem_id, a.formset_inst_id, a.receive_time FROM mv_workitem a WHERE a.receive_time IN ( SELECT MIN (t.receive_time) FROM mv_workitem t WHERE t.formset_id = 347 AND t.receiver_dept = '总裁办公室(股份)' AND t.app_type_name = '部门阅办文件' )) bb WHERE aa.workitem_id = bb.workitem_id 子查询的order by和group by都不用吧?而且这两个都是耗资源的
没测试,你自己测试下看看~ ;WITH cte AS (select max(t.workitem_id) maxWid, min(t.receive_time) minRtime from mv_workitem t where t.formset_id = 347 and t.receiver_dept = '总裁办公室(股份)' and t.app_type_name = '部门阅办文件' group by t.formset_inst_id ) SELECT a.workitem_id, a.formset_inst_id, a.complete_time FROM mv_workitem a,cte c WHERE workitem_id= c.maxWid AND receive_time =c.minRtime ORDER BY workitem_id
--从你的sql分析, -- 先找到最大的 WORKITEM_ID 对应的信息 -- 再找到最小的 RECEIVE_TIME 对应的信息 -- 然后两组信息通过 WORKITEM_ID 再关联 -- 实际上相当于同时找 WORKITEM_ID 最大 和 RECEIVE_TIME 最小的信息,因此改造如下SELECT WORKITEM_ID, FORMSET_INST_ID, COMPLETE_TIME, RECEIVE_TIME FROM MV_WORKITEM A WHERE (WORKITEM_ID, RECEIVE_TIME) IN (SELECT MAX(T.WORKITEM_ID), MIN(T.RECEIVE_TIME) FROM MV_WORKITEM T WHERE T.FORMSET_ID = 347 AND T.RECEIVER_DEPT = '总裁办公室(股份)' AND T.APP_TYPE_NAME = '部门阅办文件' GROUP BY T.FORMSET_INST_ID);
--另外如果从业务逻辑看(猜测) --猜测你想找的数据是,对于每一个 FORMSET_INST_ID ,有接收时间(RECEIVE_TIME) ----有不同的处理环节(WORKITEM_ID) ----每一个处理环节有完成时间(COMPLETE_TIME) ----所以猜测你想找的数据是 ------对每一个FORMSET_INST_ID,最早收到的,最大处理环节 的信息 --测试下下面的语句是否满足你的要求吧 SELECT FORMSET_INST_ID, MAX(COMPLETE_TIME)KEEP(DENSE_RANK LAST ORDER BY WORKITEM_ID)COMPLETE_TIME MAX(WORKITEM_ID)KEEP(DENSE_RANK FIRST ORDER BY RECEIVE_TIME)WORKITEM_ID, MIN(RECEIVE_TIME)KEEP(DENSE_RANK FIRST ORDER BY RECEIVE_TIME)RECEIVE_TIME FROM MV_WORKITEM A WHERE T.FORMSET_ID = 347 AND T.RECEIVER_DEPT = '总裁办公室(股份)' AND T.APP_TYPE_NAME = '部门阅办文件' GROUP BY FORMSET_INST_ID,COMPLETE_TIME;
简单优化建议
1、第一个in子查询中的order by 排序是多余的。可以去掉。
2、对于数据量较大的情况,尽量使用表连接方式取代子查询方式。
3、注意过滤条件上的索引的使用。
SELECT aa.workitem_id,
aa.formset_inst_id,
bb.receive_time,
aa.complete_time
FROM ( SELECT a.workitem_id, a.formset_inst_id, a.complete_time
FROM mv_workitem a
WHERE workitem_id IN
( SELECT MAX (t.workitem_id)
FROM mv_workitem t
WHERE t.formset_id = 347
AND t.receiver_dept = '总裁办公室(股份)'
AND t.app_type_name = '部门阅办文件'
)
) aa,
(SELECT a.workitem_id, a.formset_inst_id, a.receive_time
FROM mv_workitem a
WHERE a.receive_time IN
( SELECT MIN (t.receive_time)
FROM mv_workitem t
WHERE t.formset_id = 347
AND t.receiver_dept = '总裁办公室(股份)'
AND t.app_type_name = '部门阅办文件'
)) bb
WHERE aa.workitem_id = bb.workitem_id
子查询的order by和group by都不用吧?而且这两个都是耗资源的
;WITH cte AS (select max(t.workitem_id) maxWid, min(t.receive_time) minRtime
from mv_workitem t
where t.formset_id = 347
and t.receiver_dept = '总裁办公室(股份)'
and t.app_type_name = '部门阅办文件'
group by t.formset_inst_id
)
SELECT a.workitem_id, a.formset_inst_id, a.complete_time
FROM mv_workitem a,cte c
WHERE workitem_id= c.maxWid AND receive_time =c.minRtime
ORDER BY workitem_id
--从你的sql分析,
-- 先找到最大的 WORKITEM_ID 对应的信息
-- 再找到最小的 RECEIVE_TIME 对应的信息
-- 然后两组信息通过 WORKITEM_ID 再关联
-- 实际上相当于同时找 WORKITEM_ID 最大 和 RECEIVE_TIME 最小的信息,因此改造如下SELECT WORKITEM_ID, FORMSET_INST_ID, COMPLETE_TIME, RECEIVE_TIME
FROM MV_WORKITEM A
WHERE (WORKITEM_ID, RECEIVE_TIME) IN
(SELECT MAX(T.WORKITEM_ID), MIN(T.RECEIVE_TIME)
FROM MV_WORKITEM T
WHERE T.FORMSET_ID = 347
AND T.RECEIVER_DEPT = '总裁办公室(股份)'
AND T.APP_TYPE_NAME = '部门阅办文件'
GROUP BY T.FORMSET_INST_ID);
--另外如果从业务逻辑看(猜测)
--猜测你想找的数据是,对于每一个 FORMSET_INST_ID ,有接收时间(RECEIVE_TIME)
----有不同的处理环节(WORKITEM_ID)
----每一个处理环节有完成时间(COMPLETE_TIME)
----所以猜测你想找的数据是
------对每一个FORMSET_INST_ID,最早收到的,最大处理环节 的信息
--测试下下面的语句是否满足你的要求吧
SELECT FORMSET_INST_ID,
MAX(COMPLETE_TIME)KEEP(DENSE_RANK LAST ORDER BY WORKITEM_ID)COMPLETE_TIME
MAX(WORKITEM_ID)KEEP(DENSE_RANK FIRST ORDER BY RECEIVE_TIME)WORKITEM_ID,
MIN(RECEIVE_TIME)KEEP(DENSE_RANK FIRST ORDER BY RECEIVE_TIME)RECEIVE_TIME
FROM MV_WORKITEM A
WHERE T.FORMSET_ID = 347
AND T.RECEIVER_DEPT = '总裁办公室(股份)'
AND T.APP_TYPE_NAME = '部门阅办文件'
GROUP BY FORMSET_INST_ID,COMPLETE_TIME;
--第二句最后修改下
GROUP BY FORMSET_INST_ID;