ITPub博客

首页 > 数据库 > Oracle > oracle 快速删除和快速插入的方法之一

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

原创 Oracle 作者:lishiran 时间:2007-11-04 22:30:02 0 删除 编辑
快速插入[@more@]

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;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9099175/viewspace-980794/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Ruby介绍
请登录后发表评论 登录
全部评论
  • 博文量
    109
  • 访问量
    514510