之前一个存储过程执行速度随着数据量的增加而越来越慢,当处理的数据量达到10万多时,需要时间在十几个小时以上,让人很是苦恼;
后来在网上查资料,发现UPDATE语句是比较耗时的一个过程,而我的存储过程里UPDATE语句就有好几个,而且每个都要处理十多万条记录,而且都涉及子查询,关联的表有三四张这样,网上说这个时候需要用游标或者MERGE来实现;
-----------------------------------(也试着用游标实现了一下,发现时间只是缩短到了三分之一大概,而MERGE INTO 就快了好多,所以在此主要记录MERGE)
以下是UPDATE语句和MERGE语句的对比:
UPDATE:
语句:
UPDATE T_KF_PIECE_DOWNLOAD_WORK_LIST T
SET T.ACCEPTINSTITUTION =
(SELECT DEPT_NAME
FROM (SELECT T.WORKLISTNO,
TO_CHAR(T.ADDDATE, 'YYYYMMDD'),
T.ACCEPTINSTITUTION,
DEP.DEPT_NAME
FROM T_KF_PIECE_DOWNLOAD_WORK_LIST T,
T_DW_KF_SYS_DEPARTMENT DEP
WHERE TO_CHAR(T.ADDDATE, 'YYYYMMDD') >= 20140101
AND T.ACCEPTINSTITUTION = DEP.DEPT_NO
AND T.ACCEPTINSTITUTION IS NOT NULL
UNION ALL
SELECT T.WORKLISTNO,
TO_CHAR(T.ADDDATE, 'YYYYMMDD'),
T.ACCEPTINSTITUTION,
NULL
FROM T_KF_PIECE_DOWNLOAD_WORK_LIST T
WHERE TO_CHAR(T.ADDDATE, 'YYYYMMDD') >= 20140101
AND T.ACCEPTINSTITUTION IS NULL) TAB1
WHERE T.WORKLISTNO = TAB1.WORKLISTNO);
记录:10万+条
耗时:1h+
MERGE:
语句:
MERGE INTO T_KF_PIECE_DOWNLOAD_WORK_LIST T
USING (SELECT T.WORKLISTNO,
TO_CHAR(T.ADDDATE, 'YYYYMMDD'),
T.ACCEPTINSTITUTION,
DEP.DEPT_NAME
FROM T_KF_PIECE_DOWNLOAD_WORK_LIST T,
T_DW_KF_SYS_DEPARTMENT DEP
WHERE TO_CHAR(T.ADDDATE, 'YYYYMMDD') >= 20140101
AND T.ACCEPTINSTITUTION = DEP.DEPT_NO
AND T.ACCEPTINSTITUTION IS NOT NULL
UNION ALL
SELECT T.WORKLISTNO,
TO_CHAR(T.ADDDATE, 'YYYYMMDD'),
T.ACCEPTINSTITUTION,
NULL
FROM T_KF_PIECE_DOWNLOAD_WORK_LIST T
WHERE TO_CHAR(T.ADDDATE, 'YYYYMMDD') >= 20140101
AND T.ACCEPTINSTITUTION IS NULL) TAB1
ON (T.WORKLISTNO = TAB1.WORKLISTNO)
WHEN MATCHED THEN
UPDATE SET T.ACCEPTINSTITUTION=TAB1.DEPT_NAME;
记录:10万+条
耗时:3s
关于MERGE INTO 语句简介:
MERGE INTO TABLE1 T_UP --------------要更新的表
USING (SELECT T2.C1,T2.C2,T3.C3,T3.C4,T3.C5 FROM TABLE2 T2
LEFT JOIN TABLE3 T3 ON T2.C1=T3.C3) T_IN ----------------数据集(也可以是一张表)----用法 USING TABLE2 T_IN
ON (T_UP.C1=T_IN.C1) ------------------关联条件
WHEN MATCHED THEN
UPDATE SET T_UP.C4=T_IN.C4,
T_UP.C5=T_IN.C5 ----------------------当匹配关联条件时,执行UPDATE(可set多个字段)------------可以到此结束,也可以不匹配时执行INSERT
WHEN NOT MATCHED THEN
INSERT VALUES(T_IN.C1,T_IN.C2,T_IN.C4,T_IN.C5);-----------------不匹配时,执行INSERT
参考:
http://www.cnblogs.com/wuyisky/archive/2010/05/27/1745021.html
http://blog.sina.com.cn/s/blog_5384afff01013x68.html