Re: performance comparision

From: UXDBA <>
Date: Fri, 29 Jan 2010 02:05:33 -0800 (PST)
Message-ID: <>

On Jan 28, 10:00 pm, joel garry <> wrote:
> On Jan 28, 9:56 am, UXDBA <> wrote:
> > > Regards
> > Thanks  Joel
> > T1 count(*) - 6469204
> Sorry I missed that in the OP.
> > and size is just 850 MB.
> > Regards
> Is there some reason you can't just do the setting of the mark for
> delete flag and do it all at once, commit at the end?  That will
> probably force it to do a full table scan, switching your sequential
> reads to scattered reads, and perhaps moving the bottleneck to redo
> writing, where it perhaps should be.
> Is your business logic too complex to just do the delete as a single,
> perhaps complicated, SQL statement?  That would also reduce PL context-
> switching, as well as doing the full-table stuff once instead of
> twice.
> There is no rule of thumb for what percentage of deletes will work
> better with a full table scan, but some people have shown situations
> where even a small percentage of the data being deleted benefits from
> just going through a full-scan once.  Sometimes this is due to the
> order of magnitude performance increase possible with multiblock
> reads.
> There is a rule of thumb about when to use sql versus pl/sql:  Oh, I
> see you posted this question to asktom too, see the answer to Tony
> Fernandez two questions above your question.
> jg
> --
> is bogus.

Thanks Joel,

...Is your business logic too complex to just do the delete as a single,

No it is simple we can mark the rows to be deleted in small chunks. and then do a simple

delete from ... where ...

yes I have seen the posting in Asktom.
Will attempt the same.

Regards Received on Fri Jan 29 2010 - 04:05:33 CST

Original text of this message