Re: TRUNCATE v DROP TABLE question
Date: 4 Feb 2002 13:20:48 -0800
Message-ID: <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:
- 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 - 22:20:48 CET