Re: performance comparision
Date: Thu, 28 Jan 2010 14:00:52 -0800 (PST)
Message-ID: <be91dd66-136d-48b9-8f81-40ccf97f78d1_at_v37g2000prh.googlegroups.com>
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 contextswitching, 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:2345591157689
jg
-- _at_home.com is bogus. http://acme.com/licensemaker/licensemaker.cgi?state=California&text=oracle&plate=1993&r=1987184252Received on Thu Jan 28 2010 - 16:00:52 CST