Oracle 利用 rowid 提升 update 性能

 

关于ROWID的介绍参考我的Blog

            Oracle Rowid 介绍

            http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx

 

关于大表Update 的一个讨论,参考itpub

            http://www.itpub.net/viewthread.php?tid=1052077

 

. 在虚拟机上 使用rowid 进行update 测试

            使用rowid 进行update能提高速度,是因为通过rowid 能够迅速的进行定位,不用全表进行扫描。

 

-- 查看表dave 记录数

SYS@dave2(db2)> select count(*) from dave;

  COUNT(*)

----------

   3080115 --300万数据

 

-- 创建测试表dba

SYS@dave2(db2)> create table dba as select * from dave;

Table created.

 

--dave 表去更新DBA

SYS@dave2(db2)> update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id);

 

3080115 rows updated.

 

Elapsed: 00:16:12.81 -- 整个更新花了16分钟

 

--update 期间查看session 执行时间:

SQL>select sid,target,time_remaining,elapsed_seconds,message,sql_id from v$session_longops where sid=138;

 

 

select * from v$lock where sid=138;

 

select * from v$session_wait where sid=138;

 

-- 使用rowid 进行更新

DECLARE

  CURSOR cur IS

    SELECT

     a.area_code, b.ROWID ROW_ID

      FROM dave a, dba b

     WHERE a.id = b.id

     ORDER BY b.ROWID;  ---如果表的数据量不是很大,可以不用 order by rowid

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;

  FOR row IN cur LOOP

    UPDATE dba

       SET prov_code = row.area_code

     WHERE ROWID = row.ROW_ID;

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;

  COMMIT;

END;

 

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:14:54.07 -- 执行花了14分钟,速度提高不是很多。

 

            在这个更新中,使用了ORDER BY b.ROWID 进行了排序,每个数据块里面都有多条记录,这样按rowid 进行排序,那么这样每次访问数据块的时候就会相同,就会减小block 在调用的次数,从而提高效率。

 

            因为我这是虚拟机上的测试环境,所以内存分配的并不合适,I/O 也不行。

 

--我们把order by 去掉,在更新看看

DECLARE

  CURSOR cur IS

    SELECT

     a.area_code, b.ROWID ROW_ID

      FROM dave a, dba b

     WHERE a.id = b.id;

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;

  FOR row IN cur LOOP

    UPDATE dba

       SET prov_code = row.area_code

     WHERE ROWID = row.ROW_ID;

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;

  COMMIT;

END;

 

 

PL/SQL procedure successfully completed.

Elapsed: 00:20:24.43

-- 居然用了21分钟,看来对大表还是很有必要进行order by rowid的。

 

. 在测试服务器上测试

            折腾了半天没有折腾出效果来。将数据dump 出来,在imp 到测试服务器,300w的数据,dump 文件有300M

 

--在测试服务器上直接update

SQL> update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id);

 

update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id)

       *

ERROR at line 1:

ORA-01013: user requested cancel of current operation

 

Elapsed: 00:20:45.04

 

一直的处理中. 被迫取消。 查看了一下session的状态:

SQL>select sid,target,time_remaining,elapsed_seconds,message,sql_id from v$session_longops where sid=197;

            等了20分钟,才8blocks,要处理到39521blocks,不知道要到那个猴年马月了。 居然比我虚拟机上测试的还慢。

 

-- 在测试服务器上使用rowid + order by

DECLARE

  CURSOR cur IS

    SELECT

     a.area_code, b.ROWID ROW_ID

      FROM dave a, dba b

     WHERE a.id = b.id

     ORDER BY b.ROWID;  ---如果表的数据量不是很大,可以不用 order by rowid

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;

  FOR row IN cur LOOP

    UPDATE dba

       SET prov_code = row.area_code

     WHERE ROWID = row.ROW_ID;

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;

  COMMIT;

END;

 

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:04:45.98

-- 总算看到效果了,4分多钟搞定,如果在生产库上,这个操作应该还会快一点。

 

-- 在测试服务器上使用rowid

 

DECLARE

  CURSOR cur IS

    SELECT

     a.area_code, b.ROWID ROW_ID

      FROM dave a, dba b

     WHERE a.id = b.id;

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;

  FOR row IN cur LOOP

    UPDATE dba

       SET prov_code = row.area_code

     WHERE ROWID = row.ROW_ID;

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;

  COMMIT;

END;

 

 

PL/SQL procedure successfully completed.

Elapsed: 00:09:06.73 -- 花了9分钟

 

         通过以上测试,验证了对于大表的update,除了使用rowid,还需要根据rowid 排序一下。

   

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

Blog http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群:62697716();   DBA2 群:62697977()   DBA3 群:62697850()  

DBA 超级群:63306533();  DBA4 群: 83829929  DBA5群: 142216823   

DBA6 群:158654907  聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

posted @ 2011-06-30 00:34  生活不是用来挥霍的  阅读(258)  评论(0编辑  收藏  举报