请问 大佬们这个段 sql 怎么优化？急

你这发给谁看？为什么要优化？要优化成什么样？

truncate table

根本不是会写 sql 的人写得出来的样子，sqlboy 如是说

@YYYYMMDDHHSS 大佬有方案吗

<h1>

select distinct RE.* from (
SELECT R.ID_ id,
to_number(T.PROC_INST_ID_) processInstanceId,
Q.TEXT_ busiType,
P.NAME_ definitionName,
TO_DATE(TO_CHAR(T.START_TIME_, 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS') createTime,
DECODE(NVL(L.PROC_INST_ID_, 0),
0,
TO_DATE(null, 'YYYY-MM-DD'),
TO_DATE(TO_CHAR(T.END_TIME_, 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS')) endTime,
R.NAME_ curName,
( SELECT USERNAME || '(' || REALNAME || ')' FROM t_System_User WHERE id = T.START_USER_ID_ ) AS startUserId,
(SELECT ur.phone from T_SYSTEM_USER ur where ur.id=T.START_USER_ID_) as sqrlxdh,
( SELECT u1.DEPARTMENT FROM t_System_User u1 WHERE id = T.START_USER_ID_ ) AS startUserXx,
(SELECT TK.ASSIGNEE_
FROM ACT_RU_TASK TK
WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_
AND TK.ASSIGNEE_ IS NOT NULL) assignee,
(SELECT UR.REALNAME
FROM T_SYSTEM_USER UR
WHERE UR.ID =
(SELECT TK.ASSIGNEE_
FROM ACT_RU_TASK TK
WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_
AND TK.ASSIGNEE_ IS NOT NULL)) assigneeName,
(SELECT ur.phone from T_SYSTEM_USER ur where ur.id=(SELECT TK.ASSIGNEE_
FROM ACT_RU_TASK TK
WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_
AND TK.ASSIGNEE_ IS NOT NULL)) as jsrlxdh
FROM ACT_HI_PROCINST T,
ACT_HI_VARINST Q,
ACT_RE_PROCDEF P,
ACT_HI_ACTINST L,
(SELECT T1.*,
ROW_NUMBER() OVER(PARTITION BY T1.PROC_INST_ID_ ORDER BY T1.START_TIME_ DESC NULLS LAST) RN
FROM ACT_HI_TASKINST T1) R,
(SELECT u.id FROM ACT_HI_PROCINST T JOIN T_SYSTEM_USER U ON T.START_USER_ID_ = U.Id ) tr
WHERE T.PROC_INST_ID_ = R.PROC_INST_ID_
AND T.PROC_INST_ID_ = Q.PROC_INST_ID_
AND Q.NAME_ = 'busiType'
AND P.ID_ = T.PROC_DEF_ID_
AND T.PROC_INST_ID_ = L.PROC_INST_ID_(+)
AND L.ACT_TYPE_(+) = 'endEvent'
AND T.START_USER_ID_ = tr.Id
AND R.RN = '1'
AND EXISTS (SELECT 1
FROM ACT_HI_TASKINST Q
WHERE Q.PROC_INST_ID_ = T.PROC_INST_ID_
AND Q.ASSIGNEE_ = 'abebf5330c71440ab6a4719e535b8129')
and not exists (
select 1 from
ACT_RU_TASK RES
LEFT JOIN ACT_RU_IDENTITYLINK I ON I.TASK_ID_ = RES.ID_
LEFT JOIN ACT_RU_EXECUTION REX ON REX.PROC_INST_ID_ = RES.PROC_INST_ID_
WHERE RES.SUSPENSION_STATE_ = 1
AND REX.PARENT_ID_ IS NULL
and res.ID_ = r.ID_
AND (
RES.ASSIGNEE_ = 'abebf5330c71440ab6a4719e535b8129'
OR (
RES.ASSIGNEE_ IS NULL
AND I.TYPE_ = 'candidate'
AND ( I.USER_ID_ = 'abebf5330c71440ab6a4719e535b8129' OR I.GROUP_ID_ IN ( SELECT g.ID_ FROM ACT_ID_GROUP g, ACT_ID_MEMBERSHIP membership WHERE g.ID_ = membership.GROUP_ID_ AND membership.USER_ID_ = 'abebf5330c71440ab6a4719e535b8129' ))
)
)
)



) RE
ORDER BY endTime DESC, createTime DESC

</h1>

直接甩一条 SQL 出来让人怎么优化。。。

@totoro52 就是 T_SYSTEM_USER 表数据很大 需要关联 ACT_HI_PROCINST 就会很慢

一看到 activiti 的引擎表就头疼

也不说这是干啥的

子查询太多了 可以关联的 不要用 in 用 exists