Re: Slow deleting records

well, here is my scoop on this delete.

Reasons that it's slow (assuming you don't have any referiential integrity)

1. it uses rollback segment.
2. index(es) exist in this table. (it have to update the index also)
3. trigger(s) exist in this table. (your trigger(s) fires on every transaction).

Options:

1. Truncate the table (with drop storage option if you want to free up the storage) (fastest).
2. Drop index(es) or disable trigger(s) if any, then delete your table, re-build index(es) or enable trigger(s).

hope this helps
Minh

Steve wrote:

> After over 10 years as a dba covering MS-SQL, Sybase, and Oracle I find the
> latter to be far the slowest at delete operations. You do have to consider
> you're rollback configuration and make sure its not wrapping excessively
> etc. You should also be aware that deletes do not reset a tables
> highwatermark which will effect your disk usage and tablescans greatly.
>
> truncate is near instant but deletes entire table contents NOT sets of data.
> delete allows you to delete sets of data based on criteria but is a
> *considerably* slower and performance dependant on disk and rollback
> configuration etc.
>
> Dave's News wrote in message ...
> >I have a table that only has 15000+ records but when I try to delete all
 the
> >records it takes forever. We benched marked it to about 15 minutes per
 1000
> >records. The only thing I can think that may cause this is that this table
> >has a long datatype field but most records are very small.
> >
> >Any ideas would be appreciated.
> >
> >
Received on Mon Nov 09 1998 - 00:00:00 CST