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.
- More flexibility and finer control of data block compression.
- More ability to control compression attributes at the table level.
- 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 BLOCKCOMPRESSSION 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.
For more information, see Database Design.