UPDATE FPT_PRDLOTACTIVITYHISTORY t SET t.SumCycleTime = ( SELECT SUM(PH.EVENTTIME - ( SELECT /* +USE_HASH(A,PH) */ MAX(A.Eventtime) FROM MES_PRODUCTHISTORY A WHERE A.FACTORYNAME = PH.FACTORYNAME AND A.TIMEKEY < PH.TIMEKEY AND a.Productname = ph.Productname AND a.ProcessOperationName = ph.OldProcessOperationName AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework') ) ) AS SumCycleTime FROM MES_PRODUCTHISTORY PH WHERE ph.factoryname = t.factoryname AND ph.LotName = t.LotName AND ph.Timekey = t.Timekey ) --, t.CycleTime = t.SumCycleTime / t.PRODUCTQUANTITY WHERE t.factoryname = 'ARRAY' AND t.Timekey >= '20230502220000' -- YYYYMMDDHH24MISS.HH AND t.Timekey < '20230502221000' -- YYYYMMDDHH24MISS.HH AND t.MappingEventName = 'Leave' AND nvl(t.Oldsubproductquantity,0) > 0 优化这个oracle sql

这个 SQL 语句可以进行以下优化：

1. 使用 EXISTS 替代子查询

可以将子查询改写为 EXISTS 子查询，因为 EXISTS 子查询只需要判断子查询是否返回结果，而不需要返回具体的值，比子查询更高效。

UPDATE FPT_PRDLOTACTIVITYHISTORY t
SET t.SumCycleTime = (
    SELECT SUM(PH.EVENTTIME - (
            SELECT /* +USE_HASH(A,PH) */
            MAX(A.Eventtime)
            FROM MES_PRODUCTHISTORY A
            WHERE A.FACTORYNAME = PH.FACTORYNAME
                AND A.TIMEKEY < PH.TIMEKEY
                AND a.Productname = ph.Productname
                AND a.ProcessOperationName = ph.OldProcessOperationName
                AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
        )
    ) AS SumCycleTime
    FROM MES_PRODUCTHISTORY PH
    WHERE ph.factoryname = t.factoryname
        AND ph.LotName = t.LotName
        AND ph.Timekey = t.Timekey
        AND EXISTS (
            SELECT 1
            FROM MES_PRODUCTHISTORY A
            WHERE A.FACTORYNAME = PH.FACTORYNAME
                AND A.TIMEKEY < PH.TIMEKEY
                AND a.Productname = ph.Productname
                AND a.ProcessOperationName = ph.OldProcessOperationName
                AND a.EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
        )
)
WHERE t.factoryname = 'ARRAY'
    AND t.Timekey >= '20230502220000'
    AND t.Timekey < '20230502221000'
    AND t.MappingEventName = 'Leave'
    AND NVL(t.Oldsubproductquantity,0) > 0;

2. 使用 JOIN 替代子查询

可以使用 JOIN 将子查询中的 MAX 函数改写为 JOIN 操作，从而提高查询效率。

UPDATE FPT_PRDLOTACTIVITYHISTORY t
SET t.SumCycleTime = (
    SELECT SUM(PH.EVENTTIME - A.MaxEventtime) AS SumCycleTime
    FROM MES_PRODUCTHISTORY PH
        INNER JOIN (
            SELECT FACTORYNAME, Productname, OldProcessOperationName, MAX(Eventtime) AS MaxEventtime
            FROM MES_PRODUCTHISTORY
            WHERE EventName IN ('Release','Receive','TrackOut','Skip','ChangeOper','Rework')
            GROUP BY FACTORYNAME, Productname, OldProcessOperationName
        ) A ON PH.FACTORYNAME = A.FACTORYNAME
            AND PH.Productname = A.Productname
            AND PH.OldProcessOperationName = A.OldProcessOperationName
            AND PH.Eventtime > A.MaxEventtime
    WHERE ph.factoryname = t.factoryname
        AND ph.LotName = t.LotName
        AND ph.Timekey = t.Timekey
)
WHERE t.factoryname = 'ARRAY'
    AND t.Timekey >= '20230502220000'
    AND t.Timekey < '20230502221000'
    AND t.MappingEventName = 'Leave'
    AND NVL(t.Oldsubproductquantity,0) > 0;

以上两种优化方式都可以有效提高 SQL 查询效率。