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.
Although 64 Kilobytes may seem like a significant number of columns that can be included in the result set, the use of wide UNICODE character columns may quickly surpass this restriction.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookTo 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 only the necessary columns in the outer SELECT statement of a query. Despite this limitation, derived tables within a query may still contain rows up to 1 Megabyte.
Wider-row support depends on DBS settings, storage format and client/protocol capabilities. Enabling 1 MB rows does not guarantee that every client response, individual VARCHAR or sort key supports that width; error 3577 can still occur.
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, there is no such thing as a free lunch. Here are the primary drawbacks of utilizing rows of 1 megabyte:
- They will consume more disk space
- Larger 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
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book