实例讲解Oracle的快速删除和快速插入

Oracle的快速删除和快速插入

快速插入

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.username2 from v$transaction a,v$session b,v$mystat c3 where a.addr = b.taddr and c.statistic# = 14 /

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.username2 from v$transaction a,v$session b,v$mystat c3 where a.addr = b.taddr and c.statistic# = 14 /

XIDUSN XIDSLOT USED_UBLK USED_UREC USERNAME---------- ------- ---------- ---------- -----1 1 13 423 CUST

快速删除

建个存储过程,达到2000条或者更多条,提交一次.

create or replace procedure p_deleteasn number(10);cursor my_cur is select * from t1;beginn:=0;for i in my_cur loop delete from t1 where <条件>;n:=n+1;if (mod(n,2000))=0 thencommit;end if;end loop;end;/