Re: TRUNCATE v DROP TABLE question

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 Mon Feb 04 2002 - 15:20:48 CST