利用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> 

更慢！！！！！！！！！

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3798057.html