Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.
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