Sorting and Default Sort Order Row Length Errors - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
Before performing the sort operation that orders the rows to be returned, the database creates a sort key which is appended to the rows. If the length of the sort key exceeds the system row maximum length of 1 MB, the operation 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 Teradata Vantage™ - Database Messages, B035-1096.

There are several possible reasons why a data row plus BYNET sort key can 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 Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - Data Types and Literals, B035-1143.
  • 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.

    This issue is not relevant if your system uses the packed64 row architecture rather than the aligned row format architecture. For more information, see Teradata Vantage™ - Database Design, B035-1094.

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.