同意楼上说的,最好能看看执行计划,来推测是否语句需要优化;同时最好能给出点大概逻辑的,都是左连接A表的数据在join的过程中并不会减少,只有在where才会有筛选,所以要通过业务逻辑判断是否有必要都全部左连接. 还有就是有其中一个语句是不是有问题(仅作探讨),以下的写法仅只能返回一行数据,就是说A表的LOT_KEY只有一个值能配上,是你想要的结果么? LEFT JOIN (SELECT ROWNUM RN, TT.* FROM ( SELECT DISTINCT T1.EQUIPMENT_KEY, T1.EQUIPMENT_NAME, T.TIME_STAMP, T.PIECE_KEY FROM WIP_TRANSACTION T INNER JOIN EMS_EQUIPMENTS T1 ON T.EQUIPMENT_KEY = T1.EQUIPMENT_KEY WHERE T.ACTIVITY IN ('TRACKOUT', 'TRACKIN') ORDER BY T.TIME_STAMP DESC ) TT ) F ON F.PIECE_KEY = A.LOT_KEY AND F.RN = 1
还有就是有其中一个语句是不是有问题(仅作探讨),以下的写法仅只能返回一行数据,就是说A表的LOT_KEY只有一个值能配上,是你想要的结果么?
LEFT JOIN
(SELECT ROWNUM RN,
TT.*
FROM
( SELECT DISTINCT T1.EQUIPMENT_KEY,
T1.EQUIPMENT_NAME,
T.TIME_STAMP,
T.PIECE_KEY
FROM WIP_TRANSACTION T
INNER JOIN EMS_EQUIPMENTS T1
ON T.EQUIPMENT_KEY = T1.EQUIPMENT_KEY
WHERE T.ACTIVITY IN ('TRACKOUT', 'TRACKIN')
ORDER BY T.TIME_STAMP DESC
) TT
) F ON F.PIECE_KEY = A.LOT_KEY
AND F.RN = 1