Teradata Row Size – Improvements for Strategic Queries

Roland Wenzlofsky

October 4, 2017

minutes reading time

From time to time, you may have experienced the following error when running an SQL SELECT statement, which is related to the Teradata row size limits:

3577 Row size or Sort Key size overflow

There were always limits on the number of bytes a data row in Teradata could hold. A long time ago (before Teradata Release 14.10), a 64 Kilobyte Teradata row size limit was in place for all intermediate spool tables (such as the result of derived tables) and the final result set.

With the introduction of large cylinder systems, the Teradata row size limit was raised for spool tables to 1 Megabyte, but the row size of the final result set still was limited to 64 Kilobytes. If I correctly remember, this improvement came with Teradata 14.10 (correct me if I am wrong).

While 64 Kilobytes sounds like a considerable number of columns that can be packed into the result set, the usage of wide UNICODE character columns could easily exceed this limit.

There are workarounds for such issues, such as using, whenever possible, character columns to LATIN. Another option is passing only the required columns to the outer SELECT of a query to stay below the 64 Kilobytes for the final result set (still being able to use rows with up to 1 Megabyte in the derived tables of a query).

With Teradata Release 16, these workarounds are not needed anymore: Additionally to the spool rows, the response rows of the result set can now be up to 1 Megabyte.

Here’s some more good news: Simultaneously, the row size for many database objects increased to 1 Megabyte. Here are the most important ones:

  • 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

1 Megabyte rows are enabled by default on Teradata 16 systems with large cylinders.

Of course, the most crucial advantage is that 1 Megabyte row can store more and broader columns. Depending on your database design strategies, this will help you reduce the number of joins (by getting rid of the vertical splitting of tables) and, therefore, may enhance your query performance.

But of course, there is “no such thing as a free lunch”. Here are the main disadvantages of 1 Megabyte rows:

  • 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

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

You might also like