oracle快速删除和快速插入的方法代码

文章作者 100test 发表时间 2008:03:31 12:19:16
来源 100Test.Com百考试题网

快速插入

SQL> insert /**//* append*/ into t 0select * from dba_objects nologging.

9157 rows created.


SQL> 0select 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 0select * from dba_objects.

9157 rows created.

SQL> 0select 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_0delete
as
n number(10).
cursor my_cur is 0select * from t1.
begin
n:=0.
for i in my_cur loop
0delete from t1 where <条件>.
n:=n 1.
if (mod(n,2000))=0 then
commit.
end if.
end loop.
end.
/