Occasionally, you may encounter the following error while executing an SQL SELECT statement, which pertains to the constraints imposed by the Teradata row size limits:
3577 Row size or Sort Key size overflow
Teradata has always had limitations on the size of its data rows. Before Teradata Release 14.10, all intermediate spool tables, including derived and final table results, were limited to a maximum size of 64 Kilobytes.
The Teradata 14.10 release raised the Teradata row size limit for spool tables to 1 Megabyte with the implementation of large cylinder systems. However, the final result set remained constrained to 64 Kilobytes. If my memory serves me right, this enhancement was introduced with Teradata 14.10.
Although 64 Kilobytes may seem like a significant amount of columns that can be included in the result set, the use of broad UNICODE character columns may quickly surpass this restriction.
To address these problems, employing character columns in the LATIN format may be helpful whenever feasible. Additionally, to ensure that the final result set remains under 64 Kilobytes, it is advisable to include the necessary columns in the outer SELECT statement of a query solely. Despite this limitation, derived tables within a query may still contain rows up to 1 Megabyte.
Teradata Release 16 eliminates the need for these workarounds. Moreover, the response rows of the result set can now hold up to 1 Megabyte in addition to the spool rows.
More good news: The row size of several database objects has now been increased to 1 Megabyte. The following are the most significant:
- Base table rows
- Global temporary table rows
- Volatile table rows
- The rows of Queue tables
- Columnar rows
- The Rows of USI and NUSI
- The rows of Join Indexes
- The Rows of Hash Indexes
- The Output rows of Stored Procedures
Teradata 16 systems with large cylinders have 1-megabyte rows enabled by default.
The primary benefit of a 1 Megabyte row is its ability to accommodate a greater number of wider columns. By implementing this design approach, it is possible to minimize the need for vertical table splitting, reducing the number of joins required and significantly improving query performance.
Naturally, the notion of a complimentary meal is merely a fallacy. Here are the primary drawbacks of utilizing rows of 1 megabyte:
- They will consume more disk space
- More extensive rows require more data to be moved between storage devices and the CPU. This can lead to decreased performance.
- The transient journal and the write-ahead log (WAL) will require more space.
Teradata Compression: Should you bother?
Compression in Teradata Columnar
Improve your Teradata Load Performance
