We recommend specifying tables that contain large-size, variable-length binary data, such as image and audio data, as compressed tables. Note, however, that compressing tables results in compression and expansion processing overhead. Therefore, use data compression in systems in which storage efficiency is more important than performance.

The compression library you use for data compression is zlib. Using zlib, HiRDB compresses data into the split compression size (default: MIN (32,000 bytes, compressed column definition length)) that was specified during table definition. During this process, HiRDB adds a header area (8 bytes) to manage the data pre- and post-compression, when it is compressed to each split compression size (this is separate from the header area added by zlib to the compressed data).

If the data size does not change from before compression to after, or if the data size is longer after compression, HiRDB stores the data without compressing it. Consequently, the addition of a header area makes the post-compression data larger than the pre-compression data in some cases. The following figure shows an example of pre- and post-compression data.

Figure 3-22 Pre- and post-compression data

Specify compression (COMPRESSED) in the column definition of the CREATE TABLE definition SQL. As needed, also specify the split compression size. Note that the following restrictions apply to compression specification:

• You must specify compression by column (you cannot specify compression by table).
• You can specify compression only for columns with the following data types:
  
  • BINARY type with a defined size of 256 bytes or larger
  • Abstract data type (XML type)^#

#

To compress data of a column with the abstract data type (XML type), you must have HiRDB XML Extension, Version 09-03 or newer.

• When the data of a compressed column is processed by an SQL or utility, compression and expansion processing overhead will result.
• Although the results will vary depending on the actual data that is compressed, data compression efficiency improves as the split compression size increases. However, when the split compression size increases, executing an SQL that stores data in or extracts data from compressed columns (that is, an SQL that uses the SUBSTR or POSITION function, or that performs backend deletion updates) increases the size of the process private area that must be allocated during SQL execution.
• When a compressed table is rebalanced in shared mode, data expansion and compression are performed. Consequently, rebalancing might take longer than when there are no compressed columns. To shorten the execution time, execute rebalancing in exclusive mode.