Re: Delete is very slow

From: Darryl E Balaski <darryl_e_balaski_at_groton.pfizer.com>
Date: Thu, 19 Nov 1998 09:06:16 -0500
Message-ID: <365425D8.488@groton.pfizer.com>

Loy&Hutz wrote:
>
> Hello everybody !
> I am new in Oracle world and have this problem:
> My test server (NT4.0 Server - Oracle 8.0.4-Server with Pentium 200 and
> only 64 MB RAM) needs very long for deleting rows.
> e.g.: Delete * from Tab1 (30000 rows, 200 Cols) needs arround 15 minutes.
> all that time the server has disk access.
> is it a ram problem ? is the server always swapping pages ? Task manager
> says 90MB RAM in use.
>
> Thanks
>
> Sven

Use the Truncate Command:

> truncate <schema>.<table_name> drop storage;

The drop storage parameter tells oracle to free up all unused extents past the initial extent.

IMPORTANT NOTE: truncate does an implicit commit and DOES NOT GENTERATE ROLLBACK ENTRIES OR ARCHIVES.

                  Once you issue it -- it is done -- PERIOD.      Be

Careful.

I have mentioned this before, if deletes and other transactions are taking far too long then examine your redo logs for exessive switchs and contention. Also examine your rollback segments for size, wraps and extentions.

Sincerely,
darryl dB Balaski
DBA, Pfizer Central Research Received on Thu Nov 19 1998 - 08:06:16 CST