merge into优化sql（转）
分类： Oracle
2017-04-13 10:55:07
说说背景：开发有个需求，需要对新加的一个字段根据特定的业务逻辑更新数据。
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',
'CTSCUK','CTSFAUS','CTSFCHS','CTSFDES','CTSFEBT','CTSFFIS','CTSFJPS','CTSFNLS','CTSFSES','CTSFUKG','CTSRFAD','CTSRFHL','CTSRFRB','CTSRGAR','CTSRGFI','CTSRGTY',
'CTSRM15','CTSRMAR','CTSRMFI','CTSRMFL','CTSROTR','CTSRSTP','CTSRT30','CTSRTIP','CTSRVAD','CTSRYAC','CTSRYAR','CTSRYFI','CTSRYS1','CTSRYTY')
)
AND a.BUY_SELL='B'
AND a.TRADE_STATUS='ACTV'
AND a.OPEN_LOT_QTY IS NULL;
这条SQL执行计划如下：
可以看到COST非常高，而且还有大表的全表扫描。 执行时间要4个多小时。
MERGE INTO 改写的SQL：
MERGE INTO TPS_TRADE a
USING TPS_ACCOUNT b
ON (a.ID = b.TPS_TRADE_FK_ID AND b.ACCOUNT_MNEMONIC IN ('CTSCCLH','CTSRNHT','CTSRTHT','CTSRYCP','CTSCAU','CTSCCB',
'CTSCCLO','CTSCCR','CTSCGG','CTSCOA','CTSCSL1','CTSCSL2',
'CTSCSRI','CTSCUK','CTSFAUS','CTSFCHS','CTSFDES','CTSFEBT',
'CTSFFIS','CTSFJPS','CTSFNLS','CTSFSES','CTSFUKG','CTSRFAD',
'CTSRFHL','CTSRFRB','CTSRGAR','CTSRGFI','CTSRGTY','CTSRM15',
'CTSRMAR','CTSRMFI','CTSRMFL','CTSROTR','CTSRSTP','CTSRT30',
'CTSRTIP','CTSRVAD','CTSRYAC','CTSRYAR','CTSRYFI','CTSRYS1','CTSRYTY')
AND a.BUY_SELL='B'
AND a.TRADE_STATUS='ACTV')
WHEN MATCHED THEN
UPDATE SET 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'
) ;
执行计划：
可以看到COST下降到86367，也消除了全表扫描。执行时间更是下降到了秒级，只需要不到2秒的时间。
在SQL改写的时候，首先要遵循的就是逻辑不能变，在开发写的SQL中有个限制条件是a.OPEN_LOT_QTY IS NULL，而在改写成MERGE INTO方法时，我也是把这个条件放在USING的ON条件里的，这样是不可以的，报下面的错误：
ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."OPEN_LOT_QTY"
38104. 00000 - "Columns referenced in the ON Clause cannot be updated: %s"
*Cause: LHS of UPDATE SET contains the columns referenced in the ON Clause
根据错误提示可以看到，ON条件里的列是不能被UPDATE的。 后来经过确认，发现这张表里所有的OPEN_LOT_QTY都是NULL的，所以就把这个条件从ON里去掉，完成优化。
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',
'CTSCUK','CTSFAUS','CTSFCHS','CTSFDES','CTSFEBT','CTSFFIS','CTSFJPS','CTSFNLS','CTSFSES','CTSFUKG','CTSRFAD','CTSRFHL','CTSRFRB','CTSRGAR','CTSRGFI','CTSRGTY',
'CTSRM15','CTSRMAR','CTSRMFI','CTSRMFL','CTSROTR','CTSRSTP','CTSRT30','CTSRTIP','CTSRVAD','CTSRYAC','CTSRYAR','CTSRYFI','CTSRYS1','CTSRYTY')
)
AND a.BUY_SELL='B'
AND a.TRADE_STATUS='ACTV'
AND a.OPEN_LOT_QTY IS NULL;
这条SQL执行计划如下：
可以看到COST非常高，而且还有大表的全表扫描。 执行时间要4个多小时。
MERGE INTO 改写的SQL：
MERGE INTO TPS_TRADE a
USING TPS_ACCOUNT b
ON (a.ID = b.TPS_TRADE_FK_ID AND b.ACCOUNT_MNEMONIC IN ('CTSCCLH','CTSRNHT','CTSRTHT','CTSRYCP','CTSCAU','CTSCCB',
'CTSCCLO','CTSCCR','CTSCGG','CTSCOA','CTSCSL1','CTSCSL2',
'CTSCSRI','CTSCUK','CTSFAUS','CTSFCHS','CTSFDES','CTSFEBT',
'CTSFFIS','CTSFJPS','CTSFNLS','CTSFSES','CTSFUKG','CTSRFAD',
'CTSRFHL','CTSRFRB','CTSRGAR','CTSRGFI','CTSRGTY','CTSRM15',
'CTSRMAR','CTSRMFI','CTSRMFL','CTSROTR','CTSRSTP','CTSRT30',
'CTSRTIP','CTSRVAD','CTSRYAC','CTSRYAR','CTSRYFI','CTSRYS1','CTSRYTY')
AND a.BUY_SELL='B'
AND a.TRADE_STATUS='ACTV')
WHEN MATCHED THEN
UPDATE SET 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'
) ;
执行计划：
可以看到COST下降到86367，也消除了全表扫描。执行时间更是下降到了秒级，只需要不到2秒的时间。
在SQL改写的时候，首先要遵循的就是逻辑不能变，在开发写的SQL中有个限制条件是a.OPEN_LOT_QTY IS NULL，而在改写成MERGE INTO方法时，我也是把这个条件放在USING的ON条件里的，这样是不可以的，报下面的错误：
ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."OPEN_LOT_QTY"
38104. 00000 - "Columns referenced in the ON Clause cannot be updated: %s"
*Cause: LHS of UPDATE SET contains the columns referenced in the ON Clause
根据错误提示可以看到，ON条件里的列是不能被UPDATE的。 后来经过确认，发现这张表里所有的OPEN_LOT_QTY都是NULL的，所以就把这个条件从ON里去掉，完成优化。
