Re: Speeding up deletes

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). Received on Tue Oct 13 1998 - 12:17:24 CDT