Compression Table-Level Option (Compression TLA) - Teradata Database - Teradata Vantage NewSQL Engine

When you create or alter a table, join index, or hash index, there are now more choices for defining data block compression in SQL. The new choices include:

• The ALWAYS value for the BLOCKCOMPRESSION option: The data blocks in the table and qualifying subtables are always compressed
• The BLOCKCOMPRESSIONALGORITHM option: The algorithm that compresses data blocks; choices include software or hardware compression
• The BLOCKCOMPRESSIONLEVEL option: The compression level the ZLIB software compression algorithm uses, with a user-selectable emphasis on either speed or greater compression

These options are table-level attributes, which define settings for a table. These options take precedence over system settings.

There is also a new value, ALWAYS, for the DBS Control utility fields for manually compressed tables.

Benefits

Considerations

• When you change the value of BLOCKCOMPRESSION from AUTOTEMP, MANUAL, or NEVER to ALWAYS, Teradata Database does not change the compression status of data blocks that existed before the change. This means that some data blocks in the table may be uncompressed and some compressed. Changing the value of BLOCKCOMPRESSION affects only new data blocks. The same thing is true if you change the BLOCKCOMPRESSION value from ALWAYS to the other options. To make the table consistent, use the FERRET [UN]COMPRESS commands.
• When you change the value of BLOCKCOMPRESSIONALGORITHM or BLOCKCOMPRESSIONLEVEL, Teradata Database does not change the compression status of data blocks that existed before the change. This means that some data blocks in the table may be compressed with previously defined algorithm or level settings. Changing these values affects only new data blocks.
• You must enable block-level compression at the system level before any table-level option can take effect.
• If BLOCKCOMPRESSIONALGORITHM is set to DEFAULT or is not specified, the compression algorithm is determined by the setting of the DBS Control utility field CompressionAlgorithm.
• These table-level attributes do not apply to VOLATILE and GLOBAL TEMPORARY tables.
• These table-level attributes cannot be used with the IMMEDIATE clause.

Additional Information

For more information, see Teradata Vantage™ - Database Design, B035-1094.