How do you compare and contrast different types of compression in Teradata?

1 What is primary index in Teradata?

Primary index is the most fundamental attribute of a table in Teradata. It determines how the rows of a table are hashed and distributed across the nodes in the system. Each table must have a primary index, which can be either unique or non-unique, and either single-column or multi-column. The primary index affects the efficiency of data access, join operations, and data redistribution.

2 What is compression in Teradata?

Compression is a technique used to reduce the storage space and improve the performance of data in Teradata. Multi-value compression (MVC) assigns a short code to frequently occurring values in a column, which can reduce the size of a column and improve the scan speed. Algorithmic compression (ALC) applies a mathematical algorithm to compress and decompress data, resulting in higher compression ratios than MVC, but at the cost of more CPU resources. Block-level compression (BLC) compresses entire data blocks on the disk, saving disk space and reducing I/O operations, but also requiring more CPU resources.

3 How to compare compression types in Teradata?

When comparing the different types of compression in Teradata, you must consider factors such as the data type and distribution of the column, the storage space and performance trade-off, and the compatibility and limitations of the compression types. For example, numeric columns are good candidates for ALC, while character columns are good for MVC. Compression can save storage space, but it can also increase CPU usage and affect performance. Moreover, not all compression types are compatible with all features and functions in Teradata. Therefore, it is essential to check the documentation and system requirements before applying compression to balance the benefits and costs of compression, as well as test the impact on queries and workloads.

4 How to contrast compression types in Teradata?

To contrast the different types of compression in Teradata, you can utilize a few tools and commands. The SHOW TABLE command displays the definition and compression type of a table, allowing you to compare the compression type of various tables with similar data. The HELP COLUMN command provides details and statistics of a column, enabling you to contrast the compression type and compression ratio of different columns within a table or across tables. Additionally, the Ferret utility is a disk space management tool that can report the compression type and compression ratio of a table or database, allowing you to compare the compression type and compression ratio of different tables or databases.

5 How does compression affect primary index in Teradata?

Compression can have a significant impact on the primary index selection and distribution in Teradata. For instance, compression can alter the size and cardinality of a column, thus affecting the hashing and distribution of rows. It can also affect the performance and efficiency of queries that use the primary index, as applying ALC to a primary index column can reduce storage space but increase CPU usage and response time. Furthermore, compression can limit the choice and flexibility of the primary index since ALC cannot be used for primary index columns, or you cannot change the primary index of a table that uses BLC.