15.00 - COMPRESS - Teradata Database

Teradata Database Utilities

Teradata Database
Release Number
Content Type
Publication ID
English (United States)
Last Update



Compresses specified types of data in specified databases and tables, or shows an estimate of what results would be if tables were compressed. If data of one of the specified types is subsequently added to the specified table or database, it will also be compressed.

Compression uses the algorithm specified by the CompressionAlgorithm setting in DBS Control.



Syntax Element



Prevents Ferret from displaying a confirmation message.


Unique identifier of the table for which data blocks will be compressed.

The table to be compressed can be uniquely identified in either of two ways:

  • Specify the name of the database to which the table belongs and the table name, separated by a period. The names individually or together must be delimited. The following formats are valid:
  • "database_name.table_name"
  • 'database_name.table_name'
  • "database_name"."table_name"
  • 'database_name'.'table_name'
  • "database_name.*"
  • 'database_name.*'
  • "database_name"."*"
  • 'database_name'.'*'
  • The asterisk denotes all tables in the given database.

  • Specify the unique numeric identifier of the table, which consists of two numeric values separated by a space. The table identifier consists of the first two numeric values returned by the “TABLEID” command. These two numbers are common to all subtables that comprise the table.
  • Note: The format of the input numbers depends on the current radix setting, which is displayed by the “RADIX” command.


    Compresses all primary base and CLOB data within specified database or table.


    Compresses all fallback base data and fallback CLOB data within specified database or table.


    Compresses all fallback base data, fallback CLOB data, and all primary CLOB data within specified database or table.


    Compresses all data except CLOB data within specified database or table.


    Compresses only data in CLOB subtables. Other table DBs are not compressed.


    Estimates data block (DB) sizes that would exist after a compress operation is performed, and estimates the CPU usage (time per DB) that would be required. Does not compress any data.

    Multiply the Table Size Factor shown in the output by the current table size to determine the estimated table size after a compress operation.

    Note: Estimates are most accurate when the table DB size is at or near the size defined by DATABLOCKSIZE (optionally specified when the table was created or altered), or defined by the PermDBSize setting in DBS Control if no DATABLOCKSIZE was specified for the table.

    The Ferret INQUIRE (or INQ) and ABORT commands can be used to check on the progress or halt this operation during command execution.

    The command progress percentage reported by the INQUIRE command can change unexpectedly if the table being compressed is being modified concurrently by another process.

    Usage Notes

    The COMPRESS command is part of the block-level Compression (BLC) feature of Teradata Database.

    BLC enables data compression at the data block (DB) level of the Teradata Database file system. Compression reduces the amount of storage required for a given amount of data. The BlockLevelCompression field of DBS Control enables and disables BLC.

    BLC can be enabled or disabled system-wide, or for various categories of tables. For Permanent data, only primary, fallback, and CLOB subtables, and join and hash indexes are eligible for BLC. Secondary indexes on primary rows are never compressed, but fallback copies of secondary indexes can be compressed to save space.

    BLC is subject to the compression-related DBS Control settings. Some data blocks in a compressed table may not be compressed for the following reasons:

  • The block consists of fewer sectors than the number specified by MinDBSectsToCompress.
  • The block size after compression would not be reduced by the percentage specified by MinPercentCompReduction.
  • The DBS Control compression setting for the specified table type is set to NEVER compress.
  • After a data block has been compressed using a particular algorithm, the data retains that compression, even if the algorithm is subsequently changed. New data added to the table is compressed using the new algorithm. If previously compressed data is updated, which requires uncompressing and recompressing the data, the currently set algorithm is used for the recompression.

    Consequently, a single subtable can exhibit a mixture of uncompressed data blocks and data blocks compressed using different compression algorithms, depending on the settings at the time the data was added to the table. To make the data consistent with respect to compression, use the Ferret COMPRESS command to compress all the data using the currently set algorithm, or use the Ferret UNCOMPRESS command to uncompress all the table data.

    The PermDBSize and JournalDBSize settings in DBS Control apply to the uncompressed size of these types of DBs.

    Ferret logs the beginning and end of COMPRESS operations. If the database is reset during the operation, the compression operation continues as part of the recovery process after the database restarts. However, compression will continue and complete only on the table that was in the process of being compressed at the time of the reset. The rest of the database will remain in the state it was prior to the reset.

    Only one instance of the COMPRESS command is allowed to run at any time on the system.

    If you receive an error when you issue the COMPRESS or UNCOMPRESS command, and you are certain there is no other instance of either command currently running, use the RESETBLCTSKCNT command to reset the count of running COMPRESS and UNCOMPRESS tasks to zero.

    Related Topics


    For more information on…


    DBS Control compression settings

    Chapter 11: “DBS Control (dbscontrol).”

    Query Banding

    SQL Data Definition Language.

    RADIX command

    “RADIX” on page 579.


    “RESETBLCTSKCNT” on page 580.

    TABLEID command

    “TABLEID” on page 619.

    UNCOMPRESS command

    “UNCOMPRESS” on page 621.