What are the steps to add a compressed fragment to a table?

Technote (troubleshooting)


What are the steps to add a compressed fragment to a table?


A DBA decides to add a new fragment to a large, previously compressed table. Occasionally it may become necessary to add a fragment, to assure even data distribution or disk optimization. When a new fragment is added, any data that was previously compressed in the table that moves to the new fragment is uncompressed when it is moved.

Diagnosing the problem

A new fragment added to a compressed table is created without compression during an alter fragment command. This can be confirmed by running the command: oncheck -pT <database:tablename>. The output of the -pT command includes

    • Index-specific information
    • Page allocation information by page type (for dbspaces)
    • The number of any compressed rows in a table or table fragment and the percentage of table or fragment rows that are compressed.
When table or fragment rows are not compressed, the "Compressed Data Summary" section does not appear in the output.

Resolving the problem

When a new fragment is to be added:

  1. If possible, drop the indexes and constraints for the table before starting. This can speed up the alter and compression operations.
  2. Collect an 'oncheck -pT' output for the table first, in order to keep track of the 'before' and 'after' compression layout, and to track the row counts that are associated with each fragment.
    • A table fragment must contain at least 2,000 rows, in order for a compression dictionary to be generated. If the fragment has less than 2000 rows of data at creation, it cannot be compressed.
    • Other methods to estimate compression are available here.
  3. Run the alter fragment command to create the new partition for the table.
  4. Collect another oncheck -pT output in order to note the row count allocation and partition number for the new fragment. If the row count is over 2000, it can be compressed.

Related information

Compressing data in tables and fragments
Illustration of compressed data & storage optimization
Estimating Compression

Document information

More support for:

Informix Servers

Software version:

11.5, 11.7

Operating system(s):

AIX, HP-UX, Linux, Mac OS X, Solaris, Windows

Software edition:

Enterprise, Ultimate

Reference #:


Modified date:


Translate my page

Content navigation