Oracle 利用 rowid 提升 update 性能

关于ROWID的介绍参考我的Blog：

Oracle Rowid介绍

http://blog.csdn.net/xujinyang/article/details/6829751

关于大表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>selectsid,target,time_remaining,elapsed_seconds,message,sql_idfromv$session_longopswheresid=138;

select*fromv$lockwheresid=138;

select*fromv$session_waitwheresid=138;

--使用rowid进行更新

DECLARE

CURSORcurIS

SELECT

a.area_code,b.ROWIDROW_ID

FROMdavea,dbab

WHEREa.id=b.id

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

V_COUNTERNUMBER;

BEGIN

V_COUNTER:=0;

FORrowINcurLOOP

UPDATEdba

SETprov_code=row.area_code

WHEREROWID=row.ROW_ID;

V_COUNTER:=V_COUNTER+1;

IF(V_COUNTER>=1000)THEN

COMMIT;

V_COUNTER:=0;

ENDIF;

ENDLOOP;

COMMIT;

END;

PL/SQL procedure successfully completed.

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

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

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

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

DECLARE

CURSORcurIS

SELECT

a.area_code,b.ROWIDROW_ID

FROMdavea,dbab

WHEREa.id=b.id;

V_COUNTERNUMBER;

BEGIN

V_COUNTER:=0;

FORrowINcurLOOP

UPDATEdba

SETprov_code=row.area_code

WHEREROWID=row.ROW_ID;

V_COUNTER:=V_COUNTER+1;

IF(V_COUNTER>=1000)THEN

COMMIT;

V_COUNTER:=0;

ENDIF;

ENDLOOP;

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>selectsid,target,time_remaining,elapsed_seconds,message,sql_idfromv$session_longopswheresid=197;

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

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

DECLARE

CURSORcurIS

SELECT

a.area_code,b.ROWIDROW_ID

FROMdavea,dbab

WHEREa.id=b.id

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

V_COUNTERNUMBER;

BEGIN

V_COUNTER:=0;

FORrowINcurLOOP

UPDATEdba

SETprov_code=row.area_code

WHEREROWID=row.ROW_ID;

V_COUNTER:=V_COUNTER+1;

IF(V_COUNTER>=1000)THEN

COMMIT;

V_COUNTER:=0;

ENDIF;

ENDLOOP;

COMMIT;

END;

PL/SQL procedure successfully completed.

Elapsed: 00:04:45.98

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

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

DECLARE

CURSORcurIS

SELECT

a.area_code,b.ROWIDROW_ID

FROMdavea,dbab

WHEREa.id=b.id;

V_COUNTERNUMBER;

BEGIN

V_COUNTER:=0;

FORrowINcurLOOP

UPDATEdba

SETprov_code=row.area_code

WHEREROWID=row.ROW_ID;

V_COUNTER:=V_COUNTER+1;

IF(V_COUNTER>=1000)THEN

COMMIT;

V_COUNTER:=0;

ENDIF;

ENDLOOP;

COMMIT;

END;

PL/SQL procedure successfully completed.

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

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

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