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

快速插入

SQL>insert/**//*+append*/intotselect*fromdba_objectsnologging;

9157rowscreated.


SQL>selecta.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username
2fromv$transactiona,v$sessionb,v$mystatc
3wherea.addr=b.taddrandc.statistic#=1
4/

XIDUSNXIDSLOTUSED_UBLKUSED_URECUSERNAME
----------------------------------------------------------------------
22111CUST

SQL>commit;

Commitcomplete.

SQL>insertintotselect*fromdba_objects;

9157rowscreated.

SQL>selecta.xidusn,a.xidslot,a.used_ublk,a.used_urec,b.username
2fromv$transactiona,v$sessionb,v$mystatc
3wherea.addr=b.taddrandc.statistic#=1
4/

XIDUSNXIDSLOTUSED_UBLKUSED_URECUSERNAME
----------------------------------------------------------------------
1113423CUST

快速删除

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

createorreplaceprocedurep_delete
as
nnumber(10);
cursormy_curisselect*fromt1;
begin
n:=0;
foriinmy_curloop
deletefromt1where<条件>;
n:=n+1;
if(mod(n,2000))=0then
commit;
endif;
endloop;
end;
/