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)
>
> 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
- This would only help deleting records,
if a) Oracle decides using the index (did you test that?)
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.
- 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
- Tables are *dropped*. But it is probably a good idea to disallow
end-users to create tables.
--
Sybrand Bakker
Senior Oracle DBA
Received on Thu Aug 10 2006 - 05:01:32 CDT
