Output of the estimate compression operation (SQL administration API)

After you run the command for estimating compression ratios, the database server displays information that shows the estimate of the compression ratio that can be achieved, along with the currently achieved compression ratio (if it exists).

Table 1. Information that an estimate_compression command displays
Column Information Displayed
est This is the estimate of the compression ratio that can be achieved with a new compression dictionary. The estimate is a percentage of space saved compared to no compression.
curr This is the estimate of the currently achieved compression ratio. This estimate is a percentage of space saved compared to no compression. 0.0% will always appear for non-compressed fragments or tables.
change This is the estimate of the percentage point gain (or possibly loss, although that should be rare) in the compression ratio that you could achieve by switching to a new compression dictionary. This is just the difference between est and curr.

If the table or fragment is not compressed, you can create a compression dictionary with the compress parameter. If the fragment is compressed, you must perform an uncompress or uncompress_offline operation, before you can compress.

partnum This is the partition number of the fragment.
coloff This value defines whether the estimate is for in-row data or simple large objects in the dbspace, as follows:
  • -1 indicates that the estimate for in-row data
  • A positive numeric value indicates that the estimate is for a partition simple large object at the offset identified by the value. The offset is the column offset in the table in bytes.
table This is the full name of the table to which the fragment belongs, in format database:owner.tablename

If you are estimating compression benefits for an index, the full name of the index appears in this column.


The following output shows that a .4 percent increase in saved space can occur if you recompress the first fragment. A 75.7 percent increase can occur if you compress the second fragment, which is not compressed. The value -1 in the coloff column indicates that in-row data is compressed.

 est   curr  change  partnum    coloff  table
 ----- ----- ------ -------------  -----------------------------
 75.7% 75.3%   +0.4  0x00200003     -1  insurance:bwilson.auto
 75.7%  0.0%  +75.7  0x00300002     -1  insurance:pchang.home

The following output shows compression estimates for in-row data (in the first row) and simple large objects at offsets 4 and 60 (in the second and third rows):

 est   curr  change  partnum    coloff  table
 ----- ----- ------ -------------  --------------------
 75.4% 71.5%  +3.9 0x00200002       -1 test:mah.table1
  5.0% 75.0%  +0.0 0x00200002        4 test:mah.table1
 75.0% 75.0%  +0.0 0x00200002       60 test:mah.table1

Output from compression estimates for tables and fragments look the same, except that the output for a table always shows all fragments in the table, while the output for a fragment only shows information for the specified fragments.

Copyright© 2018 HCL Technologies Limited