Re: TRUNCATE Table Command

In article <1995Jun22.202437.24146_at_dhnews.dehavilland.ca>, t89003_at_isdserv.dehavilland.ca (Rennick Sandra) writes:
> Lassi.Salo (Lassi.Salo_at_ivo.fi) wrote:
> : aaj_at_phantom.telecom.com.au (Tony Jambu) wrote:
> : > It is documented in the manuals. If it is causing your hassles, why
> : > not just use the REUSE STORAGE option to TRUNCATE?
.
.
> It is also my opinion that the storage parameters of the table remain
> intact. I did some additional testing after posting this message. I
> took a table that had a NEXT extent of 10M and altered it to have a
> NEXT extent of 1M. I then truncated the table with the reuse storage
> option. Immediately, I examined the table storage definition. The
> NEXT extent was reset back to 10M again. This is not the default storage
> definition for the tablespace, and I did not attempt to import any data.
>
> I can only conclude that whatever the definition was at table creation,
> it will be returned to those values after a TRUNCATE DROP STORAGE command.

Hi Sandra

Let me explain. I have a table TEMP (INITIAL 10K NEXT 50K). Before any insertion:

SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE

-------------------- ---------- ---------- ---------- -------------- -----------
------------
TEMP                      10240          5          1          10240       51200

After lots of insertion

SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE

-------------------- ---------- ---------- ---------- -------------- -----------
------------
TEMP                     727040        355         15          10240       51200

TRUNCATE TABLE TEMP REUSE STORAGE; Note that it is still using the same space

SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE

-------------------- ---------- ---------- ---------- -------------- -----------
------------
TEMP                     727040        355         15          10240       51200

TRUNCATE TABLE TEMP;
Now it is :

SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE

-------------------- ---------- ---------- ---------- -------------- -----------
------------
TEMP                      10240          5          1          10240       51200

What you may be confused with is the NEXT EXTENT . The REUSE clause means dont rebuild the table and leave its space used as it is. Hope that explains your observation.

ta
tony  

-- 
 _____       ________ / ___ |Tony Jambu, Database Consultant
  /_  _        /_ __ /      |Wizard Consulting,Aust (ACN 065934778)
 /(_)/ )(_/ \_/(///(/_)/_(  |CIS: 100250.2003_at_compuserve.com FAX: +61-3-4163559
 \_______/                  |EMAIL:TJambu_at_wizard.com.au PHONE: +61-3-4122905