Re: SQL Deletes without rollback

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Fri, 30 Apr 2004 10:53:05 +0100
Message-ID: <40922201.6050306@orindasoft.com>

stevie wrote:
> Hi
>
> I'm trying to find the fastest way to delete about 6 million records
> from a table based on an exists condition with another table.
>
> Delete from ORIGINAL O where not exists
> (select K.ID, K.AMID from KEEP K where K.ID = O.ID and K.AMID =
> O.AMID)
>
> This takes a long time (~2hrs).
>
> I thought I would turn of rollback, since I don't care about this
> operation being recoverable, with 'alter table ORIGINAL nologging' but
> I still see lots of activity in the rollback activity.
>
> Can anyone shed any light on this situation or provide any tips on
> improving the performance ?
>
> TIA
> Stevie

I don't have enough detail to give you precise answers but here are some rule-of-thumb type ideas you might want to consider:

How many indexes are on ORIGINAL? Do you need them all? Every time you delete 1 row from ORIGINAL oracle has to remove all the related index entries. If ORIGINAL has 9 indexes that's 9 times the overhead. If this is the case you might find that dropping the 'unimportant' indexes and recreating them afterwords is faster than deletion with all indexes in place.
Are you sure you are using an index to read KEEP? I mean "I've run explain plan" sure, not "there's an index on KEEP" sure :)
Try adding "AND ROWNUM <= 100000" to the end of your delete statement. You may find it that doing 100000 rows at a time is faster, especially if you commit after each delete. One of the symptons of running low on rollback space is a dramatic slowdown as transactions get bigger and bigger.

David Rolfe
Orinda Software

Orinda Software make OrindaBuild - A tool that writes Java persistance code for your tables, including tables with CLOB columns. Received on Fri Apr 30 2004 - 04:53:05 CDT