Re: performance comparision

From: UXDBA <unixdba73_at_googlemail.com>
Date: Fri, 29 Jan 2010 02:05:33 -0800 (PST)
Message-ID: <1d4ee41a-2ea2-4e65-bd2c-4bbd65e77890_at_r19g2000yqb.googlegroups.com>



On Jan 28, 10:00 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 28, 9:56 am, UXDBA <unixdb..._at_googlemail.com> 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.http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:234...
>
> jg
> --
> _at_home.com is bogus.http://acme.com/licensemaker/licensemaker.cgi?state=California&text=o...

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