利用ROWID快速执行关联更新

一.构造相关表P1,P2

create table p1(id int,name char(10));
create table p2(id int,name char(10));

二.批量插入数据
begin
for i in 1 .. 100000
loop
insert into p1 values(i,'a'||i);
end loop
;
commit;
end;

begin
for i in 1 .. 100000
loop
insert into p2 values(i,'b'||i);
end loop
;
commit;
end;

三 对比测试
本来的需求:
update p1 set name=(select name from p2 where p1.id=p2.id);

用p2表的name列来更新p1表的name列

SQL> set timing on
SQL> update p1 set name=(select name from p2 where p1.id=p2.id);

100000 rows updated.

Elapsed: 00:09:59.68

直接update花了10分钟,太慢了

利用rowid更新

DECLARE
  CURSOR cur IS
    SELECT
     b.name, a.ROWID ROW_ID
      FROM p1 a, p2 b
     WHERE a.id = b.id
     ORDER BY a.ROWID;
  V_COUNTER NUMBER;