Re: Performance problem with deleteing large number of rows from a table

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Fri, 11 Aug 2006 13:54:30 GMT
Message-ID: <q_%Cg.10582$rP1.3046@news-server.bigpond.net.au>

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:4k0fhpFa0m24U1_at_individual.net...
> On 10.08.2006 12:01, sybrandb wrote:
>> faisal.mansoor_at_gmail.com wrote:
>>> I am having performance problem with my application while deleting
>>> large number of rows from a table.
>>>
>>> How can I imporve performance of deleting large number of rows > 500000
>>> from a table with the following constraints.
>>>
>>> 1. Table does not have indexes, PK etc (This should help deletion --
>>> Hope I am right)
>
> I don't think so. Without indexes Oracle has to do a table scan to find
> all records that you want to delete. My guess would be that if you
> typically delete 10% of your 500,000 rows a table scan is way slower than
> a deletion based on an index lookup (assuming a proper index in place).
>

Hi Robert

I question claims such as "if you *typically* delete 10% of your 500,000 rows a table scan is way slower than a deletion based on an index loop". It may indeed be correct but typically it's unlikely to be the case at all.

See
http://groups.google.com/group/comp.databases.oracle.server/msg/96b20de9b49d2d6c for an explanation on why such claims are dangerous and generally incorrect.

Cheers

Richard Received on Fri Aug 11 2006 - 08:54:30 CDT