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

王朝oracle·作者佚名  2008-06-01
窄屏简体版  字體: |||超大  

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.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

快速删除

建个存储过程,达到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;

/

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航