16.10 - 1 MB PERM and Response Rows - Teradata Database

Teradata Database Release Summary

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Release Notes
featnum
B035-1098-161K

The maximum size of permanent and response spool rows is increased to 1 MB on large-cylinder systems. Data rows of up to 1 MB can be transferred between the client and Teradata Database. On large-cylinder systems, response messages between the client and Teradata Database can now be up to 16 MB in size and request messages can be up to 7 MB.

The following types of tables can now be created or altered to have 1 MB rows:
  • Base
  • Global temporary
  • Volatile
  • Temporal
  • Queue
  • Columnar

The following objects can now be created or altered to have 1 MB rows:

  • Secondary indexes
  • Join indexes
  • Hash indexes
  • Table functions
  • Table operators
  • Stored procedure output rows

The feature is enabled by default on large-cylinder systems initialized to Release 16.00. The feature is also enabled by default on large-cylinder systems upgraded to Release 16.00 if all 1 MB features are enabled in the DBS Control utility. If a DBA disabled any of the 1 MB features during earlier releases, an upgrade to Release 16.00 does not enable the disabled feature and 1 MB PERM and Response Rows is unavailable. To enable or disable any of the 1 MB features, contact the Teradata Support Center.

This feature is not available on small-cylinder systems. These include the 2650 appliance and any system that was initialized at Release 13.0.x.x or earlier and upgraded to Release 16.00 without any intervening system initialization.

Benefits

  • Allows applications to handle larger data sets, including larger SELECT results.
  • Allows tables to store more data and more columns, reducing or eliminating joins and enhancing query performance.

Considerations

  • You may need to adjust client programs to use larger data rows and larger request and response messages. Refer to the Teradata® Tools and Utilities Release Definition for details.
  • 1 MB data rows consume more disk space.
  • Queries operating on 1 MB rows need more transient journal space and a larger WAL log.
  • Operations involving 1 MB PERM rows and 1 MB response rows do not perform as fast as operations involving 64 KB rows.
  • Importing 1 MB data to and from the client is slower than importing 64 KB data.
  • This feature increases the amount of memory used by messages, segments, buffers, aggregate cache, and other areas. Some platforms with known memory constraints may see significant performance degradations when attempting to use this feature. This category includes appliance platforms configured with a low ratio of memory-per-AMP, such as the 5xxx series and 15xx series. These performance considerations are similar to those for the 1 MB data block feature, so the guidelines for when to use both features are similar.
  • The export width of Unicode columns with the UTF-8 client character set could limit PERM rows to less than 1 MB because record parcels are limited to 1 MB. This occurs because the default export width for Unicode columns with the UTF-8 client character set is 3 bytes per character. Change the export width to 2 bytes per character to work around this issue.
  • Teradata recommends that systems with small default block sizes use small hash index and join index rows for best storage performance.
  • Teradata recommends that systems using smaller rows use smaller data block sizes and that systems with larger rows use larger data block sizes.
  • The following objects are not affected by this feature and still have 64 KB maximum rows:
    • Permanent journals
    • Data Dictionary tables
    • Physical table header rows
    • LOB column rows
    • Geospatial and complex data type indexes

Additional Information

For more information on this feature, see Teradata® Database SQL Data Definition Language - Syntax and Examples, Teradata® Database Design, Teradata® Database Data Dictionary, and Teradata® Database Utilities.