Unexpected Row Length Errors: Sorting and Default Sort Order - Teradata Database

Unexpected Row Length Errors: Sorting and Default Sort Order

Before performing the sort operation that orders the rows to be returned, Teradata Database creates a sort key and appends it to the rows. If the length of the sort key exceeds the system row length limit of 64 KB, the operation fails and returns an error. Depending on the situation, the error message text is one of the following.

A data row is too long.

Maximum row length exceeded in database_object_name.

For explanations of these messages, see Messages.

There are several possible reasons why a data row plus BYNET sort key might unexpectedly exceed the spool row size limit of 1 MB, even without including any updates.

The value for the MaxDecimal DBS Control field for your system might have been changed to a larger value, which could increase the number of bytes stored for each value in a DECIMAL column to as much as 38 bytes where it might previously have been only 1, 2, 4, or 8 bytes.

See Database Design and SQL Data Types and Literals.

Your site has upgraded from a system with a 32‑bit aligned row architecture to a system with a 64‑bit architecture. This upgrade expands the size of data, both on disk and in memory, for a number of data types. The expansion is due to byte alignment restrictions that require padding to ensure alignment on 8‑byte boundaries. These restrictions do not exist for 32‑bit architectures. Therefore, the same row can be within the spool row 1 MB size boundary on a 32‑bit system, but exceed that limit on a 64‑bit system.

Note that this issue is not relevant if your system uses the packed64 row architecture rather than the aligned row format architecture. For details, see Database Design.

The BYNET only looks at the first 4096 bytes of the sort key created to sort the specified fields, so if the field the sort key is based on is greater than 4096 bytes, the key is truncated and the data may not sort in the desired order.

By default, nulls sort before all other data in a column. In other words, nulls sort low. For example, suppose you want to sort the following set of values: 5, 3, null, 1, 4, and 2.

The values sort as follows:

     null

     1

     2

     3

     4

     5

By default, the result values of a character expression are sorted in ascending order, using the collating sequence in effect for the session.