SQL优化实操案例：union all 语句该如何优化？

SQL1执行计划

SQL1改写：

通过缩小结果集来优化

PROC_INST_ID_ = (SELECT DISTINCT entityid
  FROM VBTXMASM
  WHERE txno = '817648'

缩小结果集后原执行耗时1110 ms下降到5ms                               

SQL2（SQL2同理SQL1）：

SQL2改写

SELECT 0 AS PROC_INST_ID_, BURM.USERNAME, 0 AS TASK_ID_, '' AS ASSIGNEE_, btxmas.LASTMODDATE AS START_TIME_
 , btxmas.LASTMODDATE AS END_TIME_, '' AS MESSAGE_, ROLM.ROLEDESC AS MEMO_
FROM VBTXMASM btxmas
 LEFT JOIN (
  SELECT TXNO, MAX(ACCEPTOPINION) AS ACCEPTOPINION
  FROM VLOAMASE where TXNO='817648'
 ) mase ON mase.TXNO = btxmas.TXNO
 LEFT JOIN VSECBURM BURM ON BURM.USERID = btxmas.LASTMODUSER
 LEFT JOIN (SELECT MIN(ROLEID) AS ROLEID, USERID
  FROM VSECBTRM
  GROUP BY USERID
  ) BTRM ON BTRM.USERID = BURM.USERID
 LEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEID
WHERE btxmas.txno = '817648'

--耗时：5ms

改写后的SQL整合：

START_TIME_
 , btxmas.LASTMODDATE AS END_TIME_, '' AS MESSAGE_, ROLM.ROLEDESC AS MEMO_
FROM VBTXMASM btxmas
 LEFT JOIN (
  SELECT TXNO, MAX(ACCEPTOPINION) AS ACCEPTOPINION
  FROM VLOAMASE where TXNO='817648'
 ) mase ON mase.TXNO = btxmas.TXNO
 LEFT JOIN VSECBURM BURM ON BURM.USERID = btxmas.LASTMODUSER
 LEFT JOIN (SELECT MIN(ROLEID) AS ROLEID, USERID
  FROM VSECBTRM
  GROUP BY USERID
  ) BTRM ON BTRM.USERID = BURM.USERID
 LEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEID
WHERE btxmas.txno = '817648'
UNION ALL
SELECT T.PROC_INST_ID_, BURM.USERNAME, T.ID_ AS TASK_ID_, T.ASSIGNEE_, T.START_TIME_
 , T.END_TIME_, NVL(N.MESSAGE_, '') AS MESSAGE_, ROLM.ROLEDESC AS MEMO_
FROM ST_ACT_HI_TASKINST T
 LEFT JOIN (SELECT PROC_INST_ID_,MIN(ID_) AS MIN_COMMENT_ID_, MAX(ID_) AS MAX_COMMENT_ID_
  FROM ST_ACT_HI_COMMENT where PROC_INST_ID_ = (SELECT DISTINCT entityid
  FROM VBTXMASM
  WHERE txno = '817648'
  )) A ON A.PROC_INST_ID_ = T.PROC_INST_ID_
 LEFT JOIN ST_ACT_HI_COMMENT M ON M.PROC_INST_ID_ = T.PROC_INST_ID_
  AND M.TASK_ID_ = T.ID_
  AND M.ID_ = A.MIN_COMMENT_ID_
 LEFT JOIN ST_ACT_HI_COMMENT N ON N.PROC_INST_ID_ = T.PROC_INST_ID_
  AND N.TASK_ID_ = T.ID_
  AND N.ID_ = A.MAX_COMMENT_ID_
 LEFT JOIN VSECBURM BURM ON BURM.USERID = T.ASSIGNEE_
 LEFT JOIN (SELECT ROLEID, USERID
  FROM VSECBTRM
  ) BTRM ON BTRM.USERID = BURM.USERID
 LEFT JOIN VSECROLM ROLM ON ROLM.ROLEID = BTRM.ROLEID
 LEFT JOIN ST_ACT_HI_IDENTITYLINK inde ON ROLM.ROLEDESC = inde.GROUP_ID_
WHERE m.MESSAGE_ IS NOT NULL
 AND m.MESSAGE_ = '同意'
 AND inde.TASK_ID_ = T.ID_
 AND T.PROC_INST_ID_ = (
  SELECT DISTINCT entityid
  FROM VBTXMASM
  WHERE txno = '817648'
  )
ORDER BY TASK_ID_ ASC

--耗时：7ms