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.
Resolving the problem
When a new fragment is to be added:
- If possible, drop the indexes and constraints for the table before starting. This can speed up the alter and compression operations.
- 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.
- Run the alter fragment command to create the new partition for the table.
- 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.