Re: SQL Deletes without rollback

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.