Re: fastest way of deleting all rows in a table

this could make a difference, if tables are to be deleted has triggers on them or not. when you delete rows, *before* or *after* delete triggers automatically fires whereas when you truncate that doesn't happen (triggers are skipped)

Norman Dunbar wrote:
> 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 ?
>
> a. Truncate table .....
> b. delete table .....
> c. drop table .... ( an re-create the table afterwards )
>
> can some one shed light on this ?
>
> Thanks,
>
> Best Regards,
> Shravana Kumar.
>
>
>

-- 
-Stan