Consolidating the Storage Space of a Table

For Version 8.50 and earlier XPS releases, after the database server allocates an extent to a table or to a fragment, that extent remains allocated until the table is dropped or truncated. Some tables use disk space inefficiently, such as one where successive light append operations and mass deletions result in a small amount of data sparsely occupying a large amount of disk storage.

Extended Parallel Server 8.51 introduces a new mechanism, called a repack thread, by which empty pages can be removed from a table after moving data records with the highest ROWID values to storage locations with lower ROWID values. This data movement feature is implemented by a new configuration parameter and new options to the onutil and onstat utilities:

• A new REPACK_THREAD_MAX configuration parameter can specify the maximum number of repack threads on a coserver.
• A new SET REPACK_THREAD_MAX option to the onutil utility can dynamically reset the REPACK_THREAD_MAX parameter at run time.
• A new REPACK DATA command-line option to the onutil utility can initiate data movement for a specified table.
• A new pak option to the onstat -g utility can display information about each repack thread and the progress of its operation.

Two data-moving algorithms, with different levels of granularity, are supported:

• For non-logging tables with no remainder pages, the repack thread moves pages, rather than individual rows.
• For tables that are logged or that have remainder pages, the repack thread moves individual rows.

Repack threads cannot operate on certain types of tables:

• static, external, and temporary tables,
• tables in the sysmaster or sysutils databases,
• indexed tables.

For more information about using repack threads to move data and free empty extents, see Consolidating Table Storage Spaces Online.