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

[hemingyang]

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, T_SYSTEM_USER U 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 T.START_USER_ID_ = U.ID AND L.ACT_TYPE_(+) = 'endEvent' 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

[l00t]

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

[npe]

truncate table

[YYYYMMDDHHSS]

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

[hemingyang]

@YYYYMMDDHHSS 大佬有方案吗

[hemingyang]

<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>

[totoro52]

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

[hemingyang]

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

[NotFoundEgg]

一看到 activiti 的引擎表就头疼

也不说这是干啥的

[jtwor]

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