Teradata 简明教程

Teradata - Compression

压缩用于减少表格使用的存储空间。在 Teradata 中，压缩可以压缩 255 个不同的值，包括 NULL。由于存储减少，因此 Teradata 可以在一个块中存储更多的记录。由于每个 I/O 操作可以处理每个块中的更多行，因此这将改善查询响应时间。可以在创建表格时使用 CREATE TABLE 添加压缩，或在创建表格后使用 ALTER TABLE 命令添加压缩。

Compression is used to reduce the storage used by the tables. In Teradata, compression can compress up to 255 distinct values including NULL. Since the storage is reduced, Teradata can store more records in a block. This results in improved query response time since any I/O operation can process more rows per block. Compression can be added at table creation using CREATE TABLE or after table creation using ALTER TABLE command.

Limitations

  1. Only 255 values can be compressed per column.

  2. Primary Index column cannot be compressed.

  3. Volatile tables cannot be compressed.

Multi-Value Compression (MVC)

下表将字段 DepatmentNo 压缩为值 1、2 和 3。当压缩应用于某一列时，该列的值不会与该行存储在一起。而是这些值存储在每个 AMP 的表头中，并且只添加表示值到行中的存在位。

The following table compresses the field DepatmentNo for values 1, 2 and 3. When compression is applied on a column, the values for this column is not stored with the row. Instead the values are stored in the Table header in each AMP and only presence bits are added to the row to indicate the value.

CREATE SET TABLE employee (
   EmployeeNo integer,
   FirstName CHAR(30),
   LastName CHAR(30),
   BirthDate DATE FORMAT 'YYYY-MM-DD-',
   JoinedDate DATE FORMAT 'YYYY-MM-DD-',
   employee_gender CHAR(1),
   DepartmentNo CHAR(02) COMPRESS(1,2,3)
)
UNIQUE PRIMARY INDEX(EmployeeNo);

多值压缩可用于处理包含有限值的大表中的列。

Multi-Value compression can be used when you have a column in a large table with finite values.