How to Analyze Teradata Tables for Multivalue Compression: A Less Costly Method Using SHOW STATISTICS VALUES COLUMN ON

Analyzing a large Teradata table with Multivalue compression incurs high workload costs.

Unfortunately, Teradata does not provide any mechanism to automate this undertaking.

Analysis typically involves tallying unique values in each table column, a time-consuming and resource-intensive process for large tables.

Fortunately, Teradata 14.10 offers a more cost-effective solution.

SHOW STATISTICS VALUES COLUMN <column> ON <table>;

This statement displays biased column values and other useful statistics. Biased values are often ideal for Teradata Multivalue compression.

This approach’s main benefit is the absence of a costly table analysis, which contrasts with the method of counting distinct column values for each column of a large table. Despite this, it still provides exceptional compression recommendations.

Considering the count of NULL values in each column is an effective starting point to reduce space usage swiftly.

While effective in producing swift outcomes, this approach is not without its downsides:

1. It only works for columns with collected and correct statistics.
2. Unfortunately, the information used in the SHOW STATISTICS statement is taken from a binary object (“FieldStatistics”). There is no easy way to extract this information. As a substitute, we have to use the text output of the statement and parse it, to be able to automate the process of creating compression statements.