Teradata 1MB Rows for Strategic Queries
From time to time you may have experienced the following error when running an SQL SELECT statement:
3577 Row size or Sort Key size overflow
There were always limits for the number of bytes a data row in Teradata was able to hold. A long time ago (before Teradata Release 14.10), a 64 Kilobyte 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, Teradata raised the limit 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 a quite huge number of columns which can be packed into the result set, the usage of wide UNICODE character columns could easily exceed this limit.
Of course, there always are some workarounds for such issues, such as limiting, whenever possible, character columns to LATIN or passing only the really needed columns to the outer SELECT of a query in order 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: At the same time the row size for many database objects has been 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 Megabytes rows are enabled by default on Teradata 16 systems with large cylinders.
Of course, the most important advantage is that 1 Megabyte rows can store more and wider columns. Depending on your database design strategies, this will help you to reduce the number of joins (by getting rid of the vertical splitting of tables) and therefore may be enhancing your query performance.
But of course, there is “no such thing as a free lunch”. Here are the main disadvantages of 1 Megabyte rows:
- Obviously, they will consume more disk space
- Bigger rows require more data to be moved between storage devices and CPU. This can lead to a decreased performance
- The transient journal and the write-ahead log (WAL) will require more space