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

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 11 Aug 2006 16:45:21 +0200
Message-ID: <4k3jg1Fa6l92U1@individual.net>

On 11.08.2006 15:54, Richard Foote wrote:
> "Robert Klemme" <shortcutter_at_googlemail.com> wrote in message
> news:4k0fhpFa0m24U1_at_individual.net...

>> On 10.08.2006 12:01, sybrandb wrote:
>>>> 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.

I stand corrected. That's an interesting read! Thanks for taking the time to write it up!

This leads me to further questions / remarks:

1. If the table is index organized am I right to assume that this will dramatically change the calculation? Using your example we would get rid of 1,000,000 LIO to table blocks. In that case the index access seems to be always superior.
2. There seem to be numerous factors that make this whole calculation even more complex (you said, you keep it simple :-)). Things I can think of: type of index, partitioning (index local or not, partition pruning possible or not...) and a whole lot others as well.
3. You mention parallelism as a major factor. Does it play in favor of one or the other? I imagine a table scan might be easier to partition.
4. Do you have any empirical data at hand?

Thanks again!

Kind regards

        robert Received on Fri Aug 11 2006 - 09:45:21 CDT