Re: Slow deleting records
Date: 1998/11/09
Message-ID: <3647B474.F3ECB05E_at_fast.net>#1/1
well, here is my scoop on this delete.
Reasons that it's slow (assuming you don't have any referiential integrity)
- it uses rollback segment.
- index(es) exist in this table. (it have to update the index also)
- trigger(s) exist in this table. (your trigger(s) fires on every transaction).
Options:
- Truncate the table (with drop storage option if you want to free up the storage) (fastest).
- 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 CET