100分求优化SQL

Question

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='部门阅办文件'  group by t.formset_inst_id) order by workitem_id)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='部门阅办文件' group by t.formset_inst_id))bb where aa.workitem_id=bb.workitem_id
查出结果105条，目前在plsql上看到的时间 1.15-1.25秒之间
刚才发的忘设置分数了  重发一个  2个一起给  一共140分

Answer 1

大致看了下，
1：in 改为EXISTS
2：内层的ORDER BY 可以去除 order by workitem_id

Answer 2

select max(t.workitem_id) from mv_workitem t where t.formset_id=347 and t.receiver_dept='总裁办公室(股份)'  and t.app_type_name='部门阅办文件'  group by t.formset_inst_id) order by workitem_id)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='部门阅办文件' group by t.formset_inst_id))bb 你的取最大和最小，就是取头尾2条记录的一个字段，可以直接用FIRST_VALUE,和LAST_VALUE句SQL实现，具体效率实际情况为主

Answer 3

没测试，你自己测试下看看
 ;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

Answer 4

-- 你的原语句：
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='部门阅办文件'  
                           group by t.formset_inst_id ) 
       order by workitem_id) 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='部门阅办文件' 
                                group by t.formset_inst_id) ) bb 
where aa.workitem_id=bb.workitem_id;-- 下面是我略加修改的语句：(你去测试一下：看执行结果是否一样？效率如何？)WITH a1 AS(select a.workitem_id, a.formset_inst_id, a.receive_time, a.complete_time 
             from mv_workitem a
            where a.formset_id=347
              and a.receiver_dept='总裁办公室(股份)'
              and a.app_type_name='部门阅办文件' ),
    a2 AS(select max(t1.workitem_id) workitem_id from a1 t1 group by t1.formset_inst_id),
    a3 AS(select t2.workitem_id, t2.formset_inst_id, t2.complete_time
             from a1 t2
            where t2.workitem_id in (select a2.workitem_id from a2),
    a3 AS(selete t3.workitem_id, t3.receive_time
            from a1 t3
           where t3.receive_time in(select min(t4.receive_time)
                                      from a1 t4
                                     group by t4.formset_inst_id)
select a1.workitem_id, a1.formset_inst_id, a3.receive_time, a1.complete_time
from a1, a3
where a1.workitem_id=a3.workitem_id;

Answer 5

SELECT m.workitem_id,
       m.formset_inst_id,
       m.complete_time,
       m.receive_time
  FROM (SELECT t.workitem_id,
               t.formset_inst_id,
               t.complete_time,
               t.receive_time,
               RANK() OVER(PARTITION BY t.formset_inst_id ORDER BY t.workitem_id DESC) workitem_id_rn,
               RANK() OVER(PARTITION BY t.formset_inst_id ORDER BY t.receive_time ASC) receive_time_rn
          FROM mv_workitem t
         WHERE t.formset_id = 347
           AND t.receiver_dept = '总裁办公室(股份)'
           AND t.app_type_name = '部门阅办文件'
          ) m
 WHERE m.workitem_id_rn = m.receive_time_rn

Answer 6

少个条件，改下SELECT m.workitem_id,
       m.formset_inst_id,
       m.complete_time,
       m.receive_time
  FROM (SELECT t.workitem_id,
               t.formset_inst_id,
               t.complete_time,
               t.receive_time,
               RANK() OVER(PARTITION BY t.formset_inst_id ORDER BY t.workitem_id DESC) workitem_id_rn,
               RANK() OVER(PARTITION BY t.formset_inst_id ORDER BY t.receive_time ASC) receive_time_rn
          FROM mv_workitem t
         WHERE t.formset_id = 347
           AND t.receiver_dept = '总裁办公室(股份)'
           AND t.app_type_name = '部门阅办文件'
          ) m
 WHERE m.workitem_id_rn = 1
   AND m.receive_time_rn = 1

Answer 7

--从你的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;

Answer 8

--第二句最后修改下
GROUP BY FORMSET_INST_ID;