Re: Speeding up deletes

In article <7001v4$3f5$1_at_news.eecs.umich.edu>,   fields_at_zip.eecs.umich.edu (Dr.Matt) wrote:
> In a certain newsgroup called comp.databases.oracle.server,
> there was a poster called <tojones_at_gate.net>,
> for it means Tim Jones . And the
> Lord Bob spoke unto Tim Jones and bade
> Tim Jones relay
> <3623837F.1A8D_at_gate.net> unto the people, saying:
>
> >Arthur Langham wrote:
> >>
> >> Sorry, forgot to mention:
> >>
> >> delete from table_name where table_field = some_criteria;
> >>
> >> Arthur Langham wrote in message ...
> >> >I need to speed up deletes. I'm looking into not logging the transaction
(I
> >> >don't care if I can rollback), though I don't know if this is possible.
Any
> >> >suggestions?
> >> >
> >> >
> >Ouch!!!! In that case.... forget my suggestion (TRUNCATE)... Sorry!!!
>
> Another possibility, if you're going to delete most of the rows
> in the table:
> 1) create a temporary table with fields the same as the original but with
> no constraints and no indices.
> 2) establish a full-table lock on the main table.
> 3) insert (select * from main_table where inverse_of_criteria()) into
> temp_table
> 4) truncate main table
> 5) insert (select * from temp_table) into main_table
> 6) commit
>
> Don't try this on any production tables until you've debugged it
> on test cases. Sometimes moving the rows aside into a non-indexed
> buffer can save time, and the truncate will truncate the indices
> as well as the main table in O(1).

Alternatively just create an index on the "table_field" column(s). For a large table where you only want to delete a few records, this can make a huge difference.

If most of the records are going to be deleted then the "insert into temp, trunk orig table, insert from temp" approach may be better, though as you mention it would be sensible to be very careful when truncating important tables!

--
Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Oct 14 1998 - 10:10:06 CDT