Re: fastest way of deleting all rows in a table

Hi Norman,

Note that truncate always resets the HWM back to the very start (ie. blocks = zip)

Cheers

Richard
"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA7035CE34E_at_lnewton.leeds.lfs.co.uk...
> 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.
>
>
>
Received on Fri Feb 28 2003 - 09:16:33 CST