How to Analyze Teradata Tables for Multivalue Compression: A Less Costly Method Using SHOW STATISTICS VALUES COLUMN ON
Learn how to analyze Teradata tables for Multivalue compression without costly operations by using the SHOW STATISTICS VALUES COLUMN statement. This method delivers excellent compression suggestions with less resource and time consumption than traditional approaches. However, it only works for colum
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.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
Fortunately, Teradata 14.10 offers a more cost-effective solution.
Get the next issue by email.
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:
- It only works for columns with collected and correct statistics.
- 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.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.