oracle 快速删除和快速插入的方法之一

快速插入

SQL > insert /**/ /*+append*/ into t select * from dba_objectsnologging;

9157 rowscreated.


SQL > select a.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username
2 from v$ transaction a,v$sessionb,v$mystatc
3 where a.addr = b.taddr and c.statistic# = 1
4 /

XIDUSNXIDSLOTUSED_UBLKUSED_URECUSERNAME
-- --------------------------------------------------------------------
2 21 1 1 CUST

SQL > commit ;

Commit complete.

SQL > insert into t select * from dba_objects;

9157 rowscreated.

SQL > select a.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username
2 from v$ transaction a,v$sessionb,v$mystatc
3 where a.addr = b.taddr and c.statistic# = 1
4 /

XIDUSNXIDSLOTUSED_UBLKUSED_URECUSERNAME
-- --------------------------------------------------------------------
1 1 13 423 CUST

快速删除

建个存储过程,达到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_curloop
delete from t1 where < 条件 > ;
n: = n + 1 ;
if (mod(n, 2000 )) = 0 then
commit ;
end if ;
end loop;
end ;
/