Re: TRUNCATE TABLE

On 27 Sep 2004 08:25:27 -0700, amitpsh_at_lycos.com (Amit Shirsikar) wrote:

>Hi,
>
>I am using command in Oracle 9i Release 2 version:
>
>TRUNCATE TABLE <table_name> REUSE STORAGE;
>
>is there be any performance issue related to REUSE STORAGE option ?
>
>Thanks in advance.
>
>Amit.

Hi Amit,

REUSE STORAGE (as opposed to DROP STORAGE) is logical if you truncate the table just to fill it up afterwards. Then the overhead of resetting HWM's, freeing extents for the table and it's indexes AND allocating them again will be saved.

However, with LMT's, that overhead is minimal.

On the contrary, a good reason for using DROP STORAGE is that the indexes on the table hereby get all extents (minus the initial) deallocated. Some of the indexes may, over time, have got skewed and thus taking up unncessary space. The deallocation of index extents with DROP STORAGE will eliminate this. (Not that DROP STORAGE is a strategic choice against skewed indexes, but never miss a chance to clean up)

In short : Use DROP STORAGE unless you have a specific reason not to.

• Kenneth Koenraadt