Re: TRUNCATE v DROP TABLE question

All good points so far.

Before you make your decision regarding truncate vs-a-vis delete, consider what account the process will run under. If the account is not the owner of the table, you will need to grant drop any table privilege which you may not particularly want to do.

"Bass Chorng" <bchorng_at_yahoo.com> wrote in message news:bd9a9a76.0202041320.6fcf124b_at_posting.google.com...
> marc_at_marcblum.de (Marc Blum) wrote in message
 news:<3c5d72d7.1947860_at_news.online.de>...
> >
> > consider TRUNCATE TABLE <tab> REUSE STORAGE;
> >
> > => data will be trucated, but extents remain allocated
> >
> If I may elaborate on this.
>
> Definitely should use this if the table is periodically
> repopulated.
>
> This gives you many folds of advantages:
>
> 1. It is immediate, vs Smon has to de-allocate all the extents
> which can take long time if the table contains lots extents.
>
> I have seen this making hours of difference.
>
> 2. It avoids enqueue contention both during de-allocation and
> allocation (when populating again). There is only one ST
> enqueue in the entire database. WHen SMON is holding ST
> enqueue for space allocation or de-allocaton, all other
> similar jobs have to wait.
>
> 3. This avoids update to uet$. Similar to 1, this can take a while
> if the table contains lots of extents.
Received on Tue Feb 05 2002 - 07:11:25 CET