利用 rowid 提升update性能

ETL 开发人员发来邮件说，能不能想办法 提升一下如下update语句的性能

UPDATE OPT_ACCT_FDIM A
SET ACCT_SKID = (SELECT ACCT_SKID
FROM OPT_ACCT_FDIM_BKP B
WHERE A.ACCT_ID = B.ACCT_ID);

SELECT COUNT(*) FROM OPT_ACCT_FDIM; -------这个表 有 226474 条数据

SELECT COUNT(*) FROM OPT_ACCT_FDIM_BKP; ------ 这个表 有227817 条数据

SELECT COUNT(*)
FROM OPT_ACCT_FDIM A, OPT_ACCT_FDIM_BKP B
WHERE A.ACCT_ID = B.ACCT_ID
AND A.ACCT_SKID <> B.ACCT_SKID; -------要更新 226474条

那么现在已经很清楚了，业务逻辑就是根据根据2个表的acct_id 字段关联，然后根据B表的字段update A表，那么这里呢 要更新整个A表

UPDATE的执行计划我们就不用看了，肯定是HASH JOIN,开发人员说 这个update 跑了30分钟，还没完成，其实我估计 这个SQL至少得1小时才能跑完。

其实，select 语句是很好优化的，但是update,delete这样的SQL， 如果要想从SQL上面优化，几乎不可能，优化update,delete我们要用PL/SQL来实现。

对于我们这里的UPDATE语句，我们可以利用rowid 来快速更新,PL/SQL 代码如下：

SQL> DECLARE
2 CURSOR CUR_B IS
3 SELECT
4 B.ACCT_ID, B.ACCT_SKID, A.ROWID ROW_ID
5 FROM OPT_ACCT_DIM A, OPT_ACCT_DIM_BKP B
6 WHERE A.ACCT_ID = B.ACCT_ID
7 ORDER BY A.ROWID; ---如果表的数据量不是很大,可以不用 order by rowid
8 V_COUNTER NUMBER;
9 BEGIN
10 V_COUNTER := 0;
11 FOR ROW_B IN CUR_B LOOP
12 UPDATE OPT_ACCT_DIM
13 SET ACCT_SKID = ROW_B.ACCT_SKID
14 WHERE ROWID = ROW_B.ROW_ID;
15 V_COUNTER := V_COUNTER + 1;
16 IF (V_COUNTER >= 1000) THEN
17 COMMIT;
18 V_COUNTER := 0;
19 END IF;
20 END LOOP;
21 COMMIT;
22 END;
23 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:21.58

现在多快啊，1分22秒搞定

其实，以前的update就相当于下面的PL/SQL代码：

declare
cursor c_update is
select b.acct_skid, a.acct_id
from opt_acct_fdim a, opt_acct_fdim_bkp b
where a.acct_id = b.acct_id;
v_counter number;
begin
v_counter := 0;
for v_row in c_update loop
update opt_acct_fdim
set acct_skid = v_row.acct_skid
where acct_id = v_row.acct_id; ---注意，这里没有rowid
v_counter := v_counter + 1;
if (v_counter >= 1000) then
commit;
v_counter := 0;
end if;
end loop;
commit;
end;
/

我自己测试了一下上面的PL/SQL 代码，跑了30分钟没跑完，为什么跑这么久呢？

其实原因就在于这里：

update opt_acct_fdim
set acct_skid = v_row.acct_skid
where acct_id = v_row.acct_id;

因为缺少 rowid定位，那么又会对表进行全表扫描，而且每更新一行就会去做全表扫描。

而我们利用rowid定位block，那么不用 全表扫描了 性能提升上 百倍。

12 UPDATE OPT_ACCT_DIM
13 SET ACCT_SKID = ROW_B.ACCT_SKID
14 WHERE ROWID = ROW_B.ROW_ID;

其实这本书 Oracle Database 10g PL/SQL 程序设计 ---清华大学出版社 p132页 里面就有这个方法

itpub 这篇帖子:http://www.itpub.net/viewthread.php?tid=1052077也提到过这个方法

总结：对于大批量的update,delete，我们可以利用rowid 来进行优化，性能往往提升 上百倍。