Understanding MaxValueLength and Its Importance in Teradata Statistics
Learn about Teradata's MaxValueLength feature, which allows users to specify how many bytes or characters should be used when creating statistics histograms.
What Is MaxValueLength Used For?
At times, gathering statistics on character columns of considerable width becomes necessary, such as VARCHAR(1000).
Prior to Teradata Release 13.10, statistics histograms were created using only the initial 16 bytes of a column, disregarding and truncating the remaining bytes.
The value "012345678901234567890" was deemed equal to "0123456789012345," resulting in both producing identical statistical data.
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.
Ultimately, this resulted in inaccurate statistics, and troubleshooting the issue proved to be arduous.
Get the next issue by email.
How MaxValueLength Improves Teradata Statistics
Teradata has introduced a solution to mitigate this issue. The new feature enables the user to define the number of bytes or characters to be considered when generating statistics histograms. This feature is called MaxValueLength.
MAXVALUELENGTH n
Character and VARCHAR columns specify a character count for statistics, while all other data types specify a byte count.
To expand the scope of collected statistics, execute the following command and specify the desired number of bytes or characters to include. For instance, if you wish to include 100 bytes (assuming that CustomerId is an INTEGER), use the following option:
COLLECT STATISTICS USING MAXVALUELENGTH 100 COLUMN (CustomerId) ON Customer;
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.