16.10 - Complex Data Type Usability and Performance Enhancements - Teradata Database

Teradata Database Release Summary

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Release Notes
featnum
B035-1098-161K
This feature provides the following enhancements for the ST_GEOMETRY, XML, and JSON complex data types (CDTs):
  • ST_GEOMETRY and XML data types can now be variable length. The maximum length for these types can be defined on a per instance basis.
  • You can specify the inline length for the ST_GEOMETRY, XML, and JSON data types. The inline length is used to specify the inline storage size.
  • ST_GEOMETRY, XML, and JSON are treated as non-LOB types if the maximum length equals the inline length.
  • The following user-defined types (UDTs) and CDTs can have multiple transform groups:
    • Structured and distinct UDTs
    • ST_GEOMETRY, XML, and JSON
    • The new DATASET data type

    Each UDT can have up to 16 transform groups. You can use the new TRANSFORM option in the CREATE PROFILE/ MODIFY PROFILE and CREATE USER/MODIFY USER statements to choose the transform group that will be used for a particular UDT.

  • You can use CREATE TRANSFORM/REPLACE TRANSFORM to create multiple transform groups in one DDL statement or add additional transforms to a UDT. However, you can still only create new transforms for structured UDTs and distinct UDTs. You cannot create new transforms for CDTs.

Benefits

  • The maximum length of an ST_GEOMETRY or XML type is now variable so the length can be adjusted in places where the type is used in a manner analogous to the VARBYTE or BLOB data types.
  • When the maximum length is the same as the inline length for the ST_GEOMETRY, XML, and JSON types, these types are treated as nonLOB types. In this case, the performance of using these types can be significantly better because there is no LOB overhead. This is particularly true when using these types in user-defined functions.
  • In addition to transform groups that convert the objects to CLOB types, the ST_GEOMETRY, XML, and JSON types will have new transform groups for VARBYTE, VARCHAR, and BLOB transformations.

Considerations

In previous releases, JSON types can always be treated as LOB types for return values and use FNC_GetJSONResultLOB in all cases, even though FNC_GetJSONInfo() indicates the return type as non-LOB. With this feature, FNC_GetJSONResultLOB no longer works for a non-LOB JSON type.

SQL Changes

  • Updated syntax for specifying the ST_GEOMETRY, XML, and JSON data types
  • CREATE TRANSFORM and REPLACE TRANSFORM
  • DROP TRANSFORM
  • CREATE PROFILE and MODIFY PROFILE
  • CREATE USER and MODIFY USER

Additional Information

For more information on Complex Data Type Usability and Performance Enhancements, see:
  • Teradata® Database Geospatial Data Types
  • Teradata® Database XML Data Type
  • Teradata® Database JSON Data Type
  • Teradata® Database SQL Data Definition Language - Syntax and Examples
  • Teradata® Database SQL Data Types and Literals