Chinaunix首页 | 论坛 | 博客
  • 博客访问: 397715
  • 博文数量: 58
  • 博客积分: 2096
  • 博客等级: 大尉
  • 技术积分: 608
  • 用 户 组: 普通用户
  • 注册时间: 2008-09-29 16:09
个人简介

专注于数据库技术研究和实践,目前就职于互联网金融企业,提供Oracle数据库技术支持和维护。 联系电话:18616803656

文章分类

全部博文(58)

文章存档

2020年(1)

2019年(4)

2018年(1)

2017年(3)

2015年(4)

2014年(7)

2012年(1)

2011年(27)

2010年(8)

2009年(2)

我的朋友

分类: 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里去掉,完成优化。



阅读(14643) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~