fastest way of deleting all rows in a table

Hi Shravana,

the subject of your post implies, to me anyway, that you want to get rid of the data in a table but keep the table (and associated indexes, privs etc) so :

Truncate table drops the high water mark of the table right down to the level that MINEXTENTS was set to, or defaulted to, on table creation. All indexes are cleared out, but still exist. Any extents above MINEXTENTS are deleted from the dictionary. If there are a large number of extents, then this can take some time, esp with dictionary managed tablespaces. No rollback is possible,

Truncate table reuse storage moves the HWM down to MINEXTENTS, clears the indexes but does not delete any extents above MINEXTENTS. This implies that you might want to reload the table - from an import perhaps - and so saves dropping the extents during the truncate and recreating them dynamically during the subsequent import. No rollback is possible.

Delete from table .... this one deletes individual rows from the table. The HWM is NOT moved at all, no extents are dropped. The table remains the same size as it was before and any sunsequent full table scans will still scan up to the HWM, so a COUNT(*) could still take 15 mins and return zero ! Can be rolled back (unless you did a commit).

Drop table bins the extents, bins the table, bins the privs, bins the indexes. Can take a while if there are many extents to deallocate - again, esp if using Dictionary Managed Tablespaces.

If you don't need the table or its data, drop the table. If you do need the table again, truncate it or truncate reuse storage according to what youare going to do with it.

HTH Regards,
Norman.

-----Original Message-----
From: ShravanaKumar [mailto:shravanakumar.ks_at_siemens.com] Posted At: Friday, February 28, 2003 1:19 PM Posted To: server
Conversation: fastest way of deleting all rows in a table Subject: fastest way of deleting all rows in a table

Hi All,

We are using Oracle 9i on Solaris 8.

I would like to know, which is the fastest way of deleting all rows from a table and why ?

1. Truncate table .....
2. delete table .....
3. drop table .... ( an re-create the table afterwards )

can some one shed light on this ?

Thanks,

Best Regards,
Shravana Kumar. Received on Fri Feb 28 2003 - 07:50:33 CST