DWHPro Insights

Understanding MaxValueLength and Its Importance in Teradata Statistics

By Roland Wenzlofsky ·

Understanding MaxValueLength and Its Importance in Teradata Statistics

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.

Ultimately, this resulted in inaccurate statistics, and troubleshooting the issue proved to be arduous.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

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
Specifies the maximum size for histogram values such as MinValue, ModeValue, MaxValue, etc.

Character and VARCHAR columns specify a character count for statistics, while all other data types specify a byte count.

The integer CustomerId example only demonstrates accepted syntax; it does not demonstrate long-character truncation. For a meaningful test, use long distinct character values sharing a prefix, collect statistics with appropriate MAXVALUELENGTH settings, and inspect stored histogram values. Limits and defaults depend on the release; successful syntax alone is not a performance result.

COLLECT STATISTICS USING MAXVALUELENGTH 100 COLUMN (CustomerId) ON Customer;

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Understanding MaxValueLength and Its Importance in Teradata Statistics”

  1. Can you show some examples in T13.10?

    So if I have a T1(col1 char(16),col2 char(20)), will the stats on (col1,col2) ignore col2? What does help stats show.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam