COMPRESS - Advanced SQL Engine - Teradata Database

Purpose

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

COMPRESS [ /Y ] table_unique [
  PRIMARY |
  FALLBACK |
  FALLBACKANDCLOBS |
  { WITHOUT | ONLY } CLOBS
] 
[ ESTIMATE | E ]

/Y

Prevents Ferret from displaying a confirmation message.

table_unique

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.
  The format of the input numbers depends on the current radix setting, which is displayed by the RADIX command.

The following options specify the type of data to compress:

PRIMARY

All primary base table data and primary LOB data that is eligible for compression within the specified database or table.

FALLBACK

All fallback base table data and fallback LOB data that is eligible for compression within the specified database or table.

FALLBACKANDCLOBS

All fallback base table data, fallback LOB data that is eligible for compression, and primary LOB data eligible for compression within the specified database or table.

WITHOUT CLOBS

All data except LOB data that is eligible for compression within the specified database or table.

ONLY CLOBS

Only the primary and fallback LOB data that is eligible for compression within the specified table. Other table DBs are not affected.

The following option does not compress any data:

ESTIMATE

E

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.

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, these include subtables for primary, fallback, eligible LOB data, and join and hash indexes. Secondary indexes on primary rows are never compressed, but fallback copies of secondary indexes can be compressed to save space.

Tables that have their BLOCKCOMPRESSION value set to NEVER in the CREATE TABLE or ALTER TABLE statement cannot be compressed using the COMPRESS command.

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 field 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