分段表rowid_利用rowid更新单表

SQL> create table test1(id int,name char(10));

Table created.

begin

for i in 1 .. 1000000

loop

insert into test1 values(i,'a'||i);

end loop;

commit;

end;

SQL> set timing on

SQL> update test1 set id=9999 where id >100;

999900 rows updated.

Elapsed: 00:01:22.37

SQL> DECLARE

CURSOR cur IS

SELECT

a.ROWID from test1 a

where a.id>100

ORDER BY a.ROWID;

V_COUNTER NUMBER;

BEGIN

V_COUNTER := 0;

FOR row IN cur LOOP

UPDATE test1

SET id = 9999

WHERE ROWID = row.ROWID;

V_COUNTER := V_COUNTER + 1;

IF (V_COUNTER >= 10000) THEN

COMMIT;

V_COUNTER := 0;

END IF;

END LOOP;

COMMIT;

END; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

22 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:50.74

SQL>

更慢！！！！！！！！！