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.

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.

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;