Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Deletes without rollback
steviehaston_at_hotmail.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).
How long should it take? Are you trying to make it faster because you need to, or because tuning is fun?
>
> 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 ?
You are trying to remove an appendix with a nuclear bomb rather than a scalpel. How many rows are in each table to start with? What is the explain plan? What does the trace look like?
Xho
-- -------------------- http://NewsReader.Com/ -------------------- Usenet Newsgroup Service $9.95/Month 30GBReceived on Tue May 04 2004 - 11:52:44 CDT