ORACLE-SQL性能优化-排序取第一条数据

最新推荐文章于 2024-04-07 11:14:24 发布

alibao

最新推荐文章于 2024-04-07 11:14:24 发布

阅读量1.6k

点赞数

文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/u013591740/article/details/128283430

版权

在数据40万左右，排序后25万左右测试如下：

方式一(快一点)：
SELECT * FROM table  WHERE  (id,createtime)  IN (SELECT id,MAX(createtime)  createtime  FROM  table  GROUP  BY  id);--获取时间最新的多条数据

方式二partition by order by(慢一点)
select a.*  FROM (
     select *  from (
            select t.*,row_number() over(partition by t.id
order by t.createtime desc) rn   from table t
) c  where rn = 1) a

--性能好一点(3S)
select TASK_PROCESSINSTID,TASK_ACTIVITYDEFID,TASK_BEGINTIME,TASK_CONTROLSTATE from V_DW_BPM_TASK
WHERE (TASK_PROCESSINSTID,TASK_ACTIVITYDEFID,TASK_BEGINTIME) IN
(
 select TASK_PROCESSINSTID,TASK_ACTIVITYDEFID,MAX(TASK_BEGINTIME) from V_DW_BPM_TASK
 inner join (
     select PROCESSDEFINITID  --流程定义ID
           ,ACTIVITYDEFID     --节点定义ID
           ,VERSIONNUM        --流程版本号
     from BO_EU_NPD_PROCESS_NODE where IS_ANALYSIS='是'
   ) N on N.ACTIVITYDEFID = TASK_ACTIVITYDEFID AND N.PROCESSDEFINITID = TASK_PROCESSDEFID
 where TASK_ACTIVITYTYPE = 'userTask' --统计人工活动节点 
   AND TASK_OWNER <> 'admin02' --admin02是默认用于系统任务创建的帐号，要排除统计
   AND TASK_CONTROLSTATE <> 'delete'  --删除的节点实例不做统计           
 GROUP  BY
   TASK_PROCESSINSTID,TASK_ACTIVITYDEFID
)


--性能差一点(7S)
select  TASK_PROCESSINSTID, TASK_ACTIVITYDEFID, TASK_CONTROLSTATE, TASK_BEGINTIME, rn
  from (
       --取节点实例中最新一条状态
        select a.*, row_number() over(partition by TASK_PROCESSINSTID,TASK_ACTIVITYDEFID order by TASK_BEGINTIME desc) rn  
        from (select TASK_PROCESSINSTID,TASK_ACTIVITYDEFID,TASK_BEGINTIME,TASK_CONTROLSTATE from V_DW_BPM_TASK
        inner join (
            select PROCESSDEFINITID  --流程定义ID
                  ,ACTIVITYDEFID     --节点定义ID
                  ,VERSIONNUM        --流程版本号
            from BO_EU_NPD_PROCESS_NODE where IS_ANALYSIS='是'
          ) N on N.ACTIVITYDEFID = TASK_ACTIVITYDEFID AND N.PROCESSDEFINITID = TASK_PROCESSDEFID
        where TASK_ACTIVITYTYPE = 'userTask' --统计人工活动节点 
          AND TASK_OWNER <> 'admin02' --admin02是默认用于系统任务创建的帐号，要排除统计
          AND TASK_CONTROLSTATE <> 'delete'  --删除的节点实例不做统计
          --AND (to_char(TASK_BEGINTIME, 'YYYYMMDD') = to_char(sysdate, 'YYYYMMDD') or to_char(TASK_ENDTIME, 'YYYYMMDD') = to_char(sysdate, 'YYYYMMDD'))
        ) a
   ) t  where t.rn = 1