Teradata Statistics MaxValueLength

Roland Wenzlofsky

May 26, 2014

minutes reading time


What Is MaxValueLength Used For?

Sometimes it is necessary to collect statistics on character columns that are very wide, such as VARCHAR(1000).

Until Teradata Release 13.10, only the first 16 bytes of the column were used to create the statistics histograms. The rest were ignored and truncated.

For example, the value “012345678901234567890” was considered equivalent to the value “0123456789012345”, which resulted in both generating identical statistical information.

In the end, this led to incorrect statistics, and when problems with the statistics occurred, it was challenging to find the cause of this fact.

How MaxValueLength Improves Teradata Statistics

To alleviate this problem, Teradata has introduced a new feature that allows the user to specify how many bytes or characters should be taken into account when creating statistics histograms: MaxValueLength

MAXVALUELENGTH n
Specifies the maximum size for histogram values such as MinValue, ModeValue, MaxValue, etc.

The number of characters for statistics on CHARACTER and VARCHAR columns is specified. For all other data types, the number of bytes.

The number of bytes or characters considered can be increased with the following option when collecting statistics. For example, if you want 100 bytes to be included (assuming CustomerId is an INTEGER), you only need to execute the following command:

COLLECT STATISTICS USING MAXVALUELENGTH 100 COLUMN (CustomerId) ON Customer;
  • 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.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >