Oracle中的快速删除和快速插入：
1.快速插入 | SQL> insert /**//*+append*/ into t select * from dba_objects nologging; 9157 rows created. SQL> select a.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username 2 from v$transaction a,v$session b,v$mystat c 3 where a.addr = b.taddr and c.statistic# = 1 4 / XIDUSN XIDSLOT USED_UBLK USED_UREC USERNAME ---------- ---------- ---------- 2 21 1 1 CUST SQL> commit; Commit complete. SQL> insert into t select * from dba_objects; 9157 rows created. SQL> select a.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username 2 from v$transaction a,v$session b,v$mystat c 3 where a.addr = b.taddr and c.statistic# = 1 4 / XIDUSN XIDSLOT USED_UBLK USED_UREC USERNAME ---------- ---------- ---------- 1 1 13 423 CUST |
2.快速删除 建一个存储过程,达到2000条或者更多条,提交一次.
| create or replace procedure p_delete as n number(10); cursor my_cur is select * from t1; begin n:=0; for i in my_cur loop delete from t1 where <条件>; n:=n+1; if (mod(n,2000))=0 then commit; end if; end loop; end; / |