Reclaiming Unused Space Within an Extent

Answer

Space allocated for table extents is committed to the table and is not available even after deleting all the data rows. The space must be reclaimed before it can be allocated to other tables.

There are three ways to reclaim the space:

1. Use ALTER FRAGMENT statement to rebuild a table. Make sure that you have enough disk space to concurrently house the fragment being deleted and the fragment being added. For more information about the syntax of ALTER FRAGMENT statement, refer to the Informix Guide to SQL: Syntax.
2. Rebuild a table by unloading the data, dropping the table, rebuilding the table, and then loading the data.
   
3. Use CLUSTER index.

If a table has indexes, choose an index on the table and run:

  ALTER INDEX index_name TO CLUSTER


index_name
The name of the index that you want to alter.


If a table has no indexes, create a new index using the CLUSTER option:

CREATE CLUSTER INDEX index_name ON table_name(column_name)


index_name
The name of the index that you want to create.


table_name
The name of the table that you want to use.


column_name
The name of the column that you want to use.


Note : This method will physically rewrite the table in the order of the index. For this to work you must have enough free disk space to store all of the table's data.