工作中经常遇到update大表的时候执行效率很低,那么怎样才能让Oracle中update数据量比较大的表执行的更快呢?先看个简单的例子吧:
需求是我们要将表INTF_CMS_CALLUSER_TPYE中的LANT_ID更新成CMS.SERV_BASE_MSG表中的LATN_ID,关联条件是INTF_CMS_CALLUSER_TPYE.OBJECT_ID=CMS.SERV_BASE_MSG.PROD_ID,目标表中INTF_CMS_CALLUSER_TPYE的数据量大概10W,源表CMS.SERV_BASE_MSG数据量大概100多万,首先写个大家一般的更新方式;
UPDATE INTF_CMS_CALLUSER_TPYE T
SET T.LANT_ID =
(SELECT C.LATN_ID
FROM CMS.SERV_BASE_MSG C
WHERE C.PROD_ID = T.OBJECT_ID)
WHERE EXISTS
(SELECT 1 FROM CMS.SERV_BASE_MSG WHERE PROD_ID = T.OBJECT_ID);
程序执行了大概半个多小时,更新成功;数据量不是很大,如果数据量更大的话大部分筒子应该不能接受,于是会有一部分人想到在object_id上创建索引,PROD_ID上已经存在索引,于是我们创建一下
CREATE INDEX IDX_INTF_CMS_OBJECT_ID ON INTF_CMS_CALLUSER_TPYE(OBJECT_ID) tablespace TBS_CMS_INTER_IDX;
接着我们再来执行一下上述更新操作:
UPDATE INTF_CMS_CALLUSER_TPYE T
SET T.LANT_ID =
(SELECT C.LATN_ID
FROM CMS.SERV_BASE_MSG C
WHERE C.PROD_ID = T.OBJECT_ID)
WHERE EXISTS
(SELECT 1 FROM CMS.SERV_BASE_MSG WHERE PROD_ID = T.OBJECT_ID);
结果不到5分钟执行完毕,是不是已经很快了
如果你还是不满意,接着我们继续改写
MERGE INTO INTF_CMS_CALLUSER_TPYE A --(目标表)
USING CMS.SERV_BASE_MSG B
ON (A.OBJECT_ID = B.PROD_ID)
WHEN MATCHED THEN
UPDATE SET A.LATN_ID = B.LATN_ID;
执行一下,6秒钟执行成功,这下大部分筒子应该都能接受吧
这个优化过程生产过程中很实用,但是要注意的是,创建索引是关键(在关联字段上的索引),merge的执行原理大家可以查阅相关资料去详细了解一下,我的博文也有相关的介绍。另外不同的Oracle版本merge的写法支持程度不一样,9i之前的版本是不支持merge的,9i才引入merge,但是9i版本是不支持单独的update的,必须要有insert的写法才算完整。