oracle提高merge更新效率,使用Merge INTO优化SQL，性能提升巨大

说说背景：开发有个需求，需要对新加的一个字段根据特定的业务逻辑更新数据。

TPS_TRADE表数据有4000多万，TPS_EXTERNAL_REF表3600多万，TPS_ACCOUNT表8200多万。

开发的SQL如下：

UPDATE TPS_TRADE a

SET a.OPEN_LOT_QTY =

(

SELECT a.trade_qty - nvl(sum(c.TRADE_QTY),0)

FROM TPS_TRADE c,

TPS_EXTERNAL_REF d

WHERE c.id=d.TPS_TRADE_FK_ID

AND c.BUY_SELL='S'

AND d.value1 = a.BO_TRADE_NUM

AND d.EXT_REF_TYPE='LINKED_LOT_ID'

AND c.TRADE_STATUS='ACTV'

)

WHERE EXISTS

(

SELECT 1 FROM TPS_ACCOUNT b

WHERE b.TPS_TRADE_FK_ID=a.id

AND b.ACCOUNT_MNEMONIC IN ('CTSCCLH','CTSRNHT','CTSRTHT','CTSRYCP','CTSCAU','CTSCCB','CTSCCLO','CTSCCR','CTSCGG','CTSCOA','CTSCSL1','CTSCSL2','CTSCSRI',