SEO

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

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

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:

1. 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.

2. 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

3. 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.

"D Rolfe" <dwrolfeFRUITBAT@orindasoft.com> wrote in message
news:40922201.6050306@orindasoft.com...
>
> 3. 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.
>

Hi David

Why does running low on rollback space cause a dramatic slowdown ?

You either have the space in which case the transaction should carry happily
along or you don't in which case the transaction will dramatically slowdown
as a car dramatically slows down when it hits a brick wall, crash and
rollback all the way.

Are you perhaps referring to dynamic allocation of extents, which should be
relatively fast with LMT or dynamic resizing of datafiles, which should be
sized correctly in the first place ?

Breaking up a logical transaction into smaller pieces for *performance*
reasons is usually ill foundered and dubious at best.

Cheers

Richard

"stevie" <steviehaston@hotmail.com> wrote in message
news:4092f6be.0404300028.56140086@posting.google.c om...
> 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

Modify your exists statement to

"paddy_nyr" <mpprpp@yahoo.com> wrote in message
news:c6tm1u$ga6qb$1@ID-197123.news.uni-berlin.de...
>
> "stevie" <steviehaston@hotmail.com> wrote in message
> news:4092f6be.0404300028.56140086@posting.google.c om...
> > 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
>
> Modify your exists statement to
>
>
Delete from ORIGINAL O where not exists
(select 1 from KEEP K where K.ID = O.ID and K.AMID =
O.AMID)

Richard,

> "D Rolfe" <dwrolfeFRUITBAT@orindasoft.com> wrote in message
> news:40922201.6050306@orindasoft.com...
>
>>3. 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.
>>
>
>
> Hi David
>
> Why does running low on rollback space cause a dramatic slowdown ?
>
> You either have the space in which case the transaction should carry happily
> along or you don't in which case the transaction will dramatically slowdown
> as a car dramatically slows down when it hits a brick wall, crash and
> rollback all the way.
>
> Are you perhaps referring to dynamic allocation of extents, which should be
> relatively fast with LMT or dynamic resizing of datafiles, which should be
> sized correctly in the first place ?
>
> Breaking up a logical transaction into smaller pieces for *performance*
> reasons is usually ill foundered and dubious at best.
>

You are right in that in most (>90%) situations the one big SQL
statement beats several smaller ones.

I have however seen several situations where breaking down a really big
transaction into chunks did lead to it running faster.

Bear in mind that I'm not telling him it *will* be faster - I'm
suggesting he try a test and see for himself....

On reflection my rollback segment theory is probably wrong....


David Rolfe
Orinda Software
Dublin, Ireland

steviehaston@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 30GB