Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with deleteing large number of rows from a table

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

From: sybrandb <sybrandb_at_gmail.com>
Date: 10 Aug 2006 03:01:32 -0700
Message-ID: <1155204092.749428.115640@i3g2000cwc.googlegroups.com>

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

  1. 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.
  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
  3. 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US