| Which method is faster!!!!! [message #370670] |
Tue, 18 January 2000 15:57  |
ban
Messages: 3
Registered: January 2000
|
Junior Member |
|
|
Hi everyone!
What is the best way to delete records from database.
If I have to delete 2 million records in the database.
With the methodII I was able to delete 150,000 records in one hour.
I didn't tried the methodI.
Which method is faster??
Long Deletes (committing every x number of records)
MethodI)
set time on
set echo on
declare
cnt number(7) := 0;
tot number(7) := 0;
cursor C1 is select rowid from <OWNER>.
where <YOUR CRITERIA>;
begin
for REC in C1 loop
delete from <OWNER>.
where rowid = REC.rowid;
tot := tot + 1;
cnt := cnt + 1;
if (cnt >= 4000) then
commit;
cnt := 0;
end if;
end loop;
commit;
dbms_output.put_line('<YOUR TABLE> records deleted: '||tot);
end;
/
------------------------------------------------------------------------------
Method II)
Declare
i integer;
Begin
For i in 1 .. 21 Loop
delete from <OWNER>.
where <YOUR CRITERIA>
and rownum<50000;
commit;
End Loop;
commit;
End;
/
|
|
|
|
| Re: Which method is faster!!!!! [message #370671 is a reply to message #370670] |
Tue, 18 January 2000 17:23  |
ban
Messages: 3
Registered: January 2000
|
Junior Member |
|
|
oops!
In MethodI)
It should be
delete from your_table
where <YOUR CRITERIA>
and rowid=REC.rowid
In MethodII)
It should be
delete from your_table
where <YOUR CRITERIA>
and rownum<50000
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
