利用 rowid 提升update性能 .

能不能想办法 提升一下如下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 来进行优化，性能往往提升 上百倍。