The STORAGE specification

The optional DROP STORAGE or REUSE STORAGE keywords specify what action the database server takes with the storage extents of the table when the TRUNCATE operation begins. If you omit this specification, the DROP STORAGE option is the default.

Using the default or explicit DROP STORAGE option, a successful TRUNCATE statement releases all but the first extent among the extents currently allocated to the table and to its indexes. You can display the current list of extents with the oncheck -pT table command. If your table has only one extent, no space will be freed.

For the following table, for example, four default eight-page extents were merged into what is now the first extent. There is also a second, larger extent:

  Extents                      
     Logical Page  Physical Page     Size Physical Pages
                0       1:104455       32             32
               32       1:104495     4576           4576

After the TRUNCATE statement runs, output from the same oncheck command displays this:

  Extents                      
     Logical Page  Physical Page     Size Physical Pages
                0       1:104455       32             32

Alternatively, if you intend to keep the same storage space allocated to the same table for subsequently loaded data, specify the REUSE STORAGE keywords to prevent the space from being deallocated. The REUSE STORAGE option of TRUNCATE can make storage management more efficient in applications where the same table is periodically emptied and reloaded with new rows.

The following example truncates the state table and frees all of its extents except the first extent:

TRUNCATE TABLE state DROP STORAGE;

The following example truncates the same table but removes only the actual data. All extents stay the same.

TRUNCATE TABLE state REUSE STORAGE;

Whether you specify DROP STORAGE or REUSE STORAGE, any out-of-row data values are released for all rows of the table when the TRUNCATE transaction is committed. Storage occupied by any BLOB or CLOB values that become unreferenced in the TRUNCATE transaction is also released.