Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Deletes without rollback

Re: SQL Deletes without rollback

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 30 Apr 2004 18:56:48 +1000
Message-ID: <409214d2$0$4547$afc38c87@news.optusnet.com.au>


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.

For starters, you're confusing rollback and redo. "NOLOGGING" switches off redo generation. Nothing switches off undo generation (discrete transactions aside), because although you might not want the undo, Oracle needs it for read-consistency purposes.

Secondly, the NOLOGGING keyword never switches of redo generation for ordinary inserts, updates and deletes, so even there you're going to be stuffed. It applies to (or is respected by, shall we say) only a very small group of DDL commands (mostly to do with index creation, SQL Loader or CTAS statements -your statement falls into none of those categories).

If things get truly desperate, you can always take a backup, disable *all* redo generation by setting a completely unsupported hidden parameter, and be prepared to kiss goodbye to your entire database (and hence have to do a total recovery) if anything goes wrong, but that is as I say a drastic tactic, and definitely not recommended.

So you either live with it, or try and improve the performance of your query (not in instead of not exists, for example... there've been lots of threads of the performance implications of the exists keyword on this group over the months... a trawl through Google might be of interest.

Regards
HJR Received on Fri Apr 30 2004 - 03:56:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US