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

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 10 Aug 2006 12:19:37 +0200
Message-ID: <4k0fhpFa0m24U1@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).

>> 2. Multiple clients can issue delete quries for deleting subset of
>> table data. (For example table might contain 50,0000 rows for product
>> A. If a user loading fresh data for product A he will first delete the
>> previous data of product A and then upload the new data, similarly
>> another user might be working with product B etc and they might be
>> running the delete query simulataneouly)
>>
>> 3. User might not have DDL rights so creating a new table and deleting
>> the old one is not possible.
>>
>> Currently we are using delete quries which is taking a lot of time.
>>
>> Faisal

> 
> 1) This would only help deleting records,
> if a) Oracle decides using the index (did you test that?)

As far as I can see he said there are *no* indexes - which seems the primary reason for slow deletion.

> and b) Oracle is wrong about the execution path because a full table
> scan would result in less I/O. In which case you should hint the delete
> statement, instead of dropping the PK which is definitely a very bad
> idea.

Yeah.

> 2) Performing a delete followed by an insert instead of an update is
> strange procedure which will consume way more resources.
> I would reconsider the procedure

That probably depends on the data but yes, one should look into this.

Kind regards

        robert Received on Thu Aug 10 2006 - 05:19:37 CDT