Re: Slow deleting records

TRUNCATE is a great answer for speeding things up, but no one so far has told this poor guy WHY deletes are slower than truncates. The short answer is one SQL keywork: ROLLBACK; The TRUNCATE command deletes the records with no possibility of doing a rollback. Those records are simply gone. When you do the DELETE, it must copy that data to the rollback segments. Only when you issue the COMMIT; command, can it actually release the information. Until you do that, Oracle must assume you might change you mind and issue a ROLLBACK; command. The reason it must do this is: if another session is querying that table, Oracle provides read consistency. Until they do a commit; Oracle must make the tables look unchanged, even after you do your commit.

So, if you cannot use the truncate command (because you want the rollback protection or because you have cascaded deletes on child tables) then you might check other things, like are your rollback segments big enough? Or try breaking up the delete, eg, instead of deleting all at once, delete on some range and do commits.

DELETE your_table
where some_column < val1;
COMMIT;
DELETE your_table
where some_column between val1 and val2; COMMIT;
DELETE your_table
where some_column between val2 and val3; COMMIT; and so on....
This reduces the size of the rollback needed for the transaction.

Dropping and recreating indices helps (don't forget that your primary key has an index associated with it too).

In article <71mfjp$i8t$1_at_mailgate.ikea.com>,   "Joachim Carlsson" <joachim.carlsson_at_neurope.ikea.com> wrote:
> When deleting all records truncate the table
>
> truncate table xxx;
>
> If you want to delete some records > 10 % drop the index if you don't have a
> very complicated look up.
>
> Regards
> /Joachim
> Dave's News wrote in message ...
> >I have a table that only has 15000+ records but when I try to delete all
 the
> >records it takes forever. We benched marked it to about 15 minutes per
 1000
> >records. The only thing I can think that may cause this is that this table
> >has a long datatype field but most records are very small.
> >
> >Any ideas would be appreciated.
> >
> >
>
>

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own