Home » RDBMS Server » Server Administration » Which method is faster!!!!!
Which method is faster!!!!! [message #370670] Tue, 18 January 2000 15:57 Go to next message
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 Go to previous messageGo to next message
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
Re: Which method is faster!!!!! [message #370673 is a reply to message #370670] Tue, 18 January 2000 17:41 Go to previous messageGo to next message
Sreenivas
Messages: 15
Registered: January 2000
Junior Member
Try using TRUNCATE table ,
this should improve performance
Re: Which method is faster!!!!! [message #370678 is a reply to message #370670] Tue, 18 January 2000 20:13 Go to previous message
ban
Messages: 3
Registered: January 2000
Junior Member
I can't truncate the table as it has some other records which I don't want change them.
Previous Topic: Delete file created bt UTL_FILE function.
Next Topic: Re: Why so slow?
Goto Forum:
  


Current Time: Fri Oct 30 02:27:11 CDT 2020