Multidimensional ARRAY/VARRAY Type Elements | CREATE TYPE | Vantage - 17.10 - Rules and Restrictions for Multidimensional ARRAY and VARRAY Data Types - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

A multidimensional ARRAY is a mapping from integer coordinates to an element type. A multidimensional ARRAY is described by a pair of lower and upper bounds for each of its dimensions.

You must create each multidimensional ARRAY/VARRAY type you need for your applications using a CREATE TYPE (ARRAY form) request. When you create a new multidimensional ARRAY/VARRAY type, you must explicitly specify the lower and upper boundaries for each dimension, as well as the element data type of the array. The element data type must be an existing Teradata SQL type. Once a multidimensional ARRAY/VARRAY type has been created, you can use it in much the same way as any other Teradata SQL data type.

The following graphic shows the constituents of a multidimensional ARRAY/VARRAY data type.


Multidimensional ARRAY/VARRAY constituents

The multidimensional ARRAY/VARRAY type is defined as a variable-length ordered list of values. It has a maximum number of values for each of its dimensions that you specify when you create the type. You can access each element value in a multidimensional ARRAY/VARRAY type using a numeric index value. For more information, see Teradata Vantage™ - Data Types and Literals, B035-1143.

Vantage supports all Teradata data types for the element type of a multidimensional ARRAY/VARRAY data type, including UDTs and Period data types with the following exceptions:
  • BLOB
  • CLOB
  • LOB UDTs (both distinct and structured types)
  • Geospatial
The following parameter data types are also not supported as element types for multidimensional ARRAY/VARRAY types.
  • VARIANT_TYPE
  • TD_ANYTYPE

You can specify a multidimensional ARRAY/VARRAY type for a table column, or for a parameter to a UDF, method, external stored procedure, or SQL stored procedure. You can also use a multidimensional ARRAY/VARRAY type to declare a local variable inside of an SQL stored procedure. Vantage supports an extended ANSI-style DML syntax to enable you to access and manipulate the individual elements of a multidimensional ARRAY/VARRAY value.

The following rules and restrictions apply to creating and using multidimensional ARRAY and VARRAY data types.
  • If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, UDT, method, or Java stored procedure object even if the new object name contains only single-byte characters. Otherwise, the system returns an error to the requestor. Instead, use a multibyte session character set.
  • As is true for structured and distinct UDTs, the database stores any ARRAY type that you create in the SYSUDTLIB database along with its autogenerated constructor UDF, by default.

    Because of this, you must have the appropriate UDT privileges on SYSUDTLIB to create an ARRAY or VARRAY type. This means that you must have either the UDTTYPE or UDTMETHOD privilege on SYSUDTLIB to create an ARRAY or VARRAY UDT.

  • You can use the optional DEFAULT NULL clause to initialize all the elements of a multidimensional ARRAY/VARRAY type to null at the time the type is created.

    This clause is particularly useful for multidimensional ARRAY/VARRAY types that are expected to be fully populated.

    When you do this, the action prevents all subset operations such as average, update, or count for multidimensional arrays from aborting and returning an error because they refer to an element that is not initialized.

  • There are two ways to specify the array boundaries for a multidimensional ARRAY/VARRAY type. You can specify the dimensions of a multidimensional ARRAY/VARRAY type using any combination of these two methods.

    Regardless of the specification method you use, an attempt to access an element that is outside the specified domain of a multidimensional ARRAY returns an error to the requestor.

    The two methods of specifying array boundaries for a multidimensional ARRAY/VARRAY type are as follows.
    • Explicitly specify lower and upper bounds for each dimension, separating the two with a colon.

      The lower bound must be less than or equal to the upper bound.

      These bounds are signed integer numbers, which means that you can specify negative numbers using this form. The upper and lower bounds are stored as 32-bit integer values internally. Therefore, the numeric range available to you for the lower and upper bounds corresponds to the maximum ranges available for an integer value, -2147483648 to 2147483647.

      You can use this range of values with the understanding that the total size of the array must be less than or equal to 64K - x, where x represents the number of extra bytes required for in-row storage of the ARRAY/VARRAY type to enable direct access to individual elements of the array and overhead for displaying the autogenerated transform string for the ARRAY/VARRAY. The size of the array is influenced not only by the number of elements, but also by the element type of the array.

    • Specify a single unsigned integer value to signify the maximum size of the dimension using ANSI-style syntax.

      ANSI-style syntax implicitly defines the lower bound of the array to be 1.

  • The maximum size of a type created as a multidimensional ARRAY/VARRAY type and its autogenerated transform string must not exceed 64 KB because the database stores its data within the row and because the maximum size of its autogenerated transform string is limited to the maximum size of a VARCHAR type, which is 64,000 Teradata Latin bytes.

    Although there is no restriction on the number of columns of a table that can be defined as a multidimensional ARRAY/VARRAY type, the expectation is that a table will generally not contain more than one column with a multidimensional ARRAY/VARRAY type, and unless the table is defined as a NoPI table, it will contain other columns, some of which might have smaller data types.

    Additionally, the row header for each row consumes an additional set of bytes, so the maximum size of a multidimensional ARRAY/VARRAY type is unlikely to exceed approximately 70% of the maximum row size. To enable the greatest degree of freedom for creators of multidimensional ARRAY/VARRAY types, the only size restriction that the database enforces is the row size limit.

    For example, if m and n are the cardinalities for dimensions 1 and 2 respectively, of a 2-dimensional ARRAY A of integer type, then A[m][n] cannot have more than (16,384 - x) elements (1 ≤ m*n ≤ (16,384-x)), where x represents the number of extra bytes required for in-row storage of the ARRAY type to enable direct access to the individual elements of the array and the overhead for displaying the autogenerated transform string for the ARRAY.

    There is a limit to the maximum number of dimensions that can be declared within the scope of the array. The minimum number of dimensions that you can create is two, and the maximum number of dimensions that you can create is five.

  • The following actions take place on the data dictionary on a CREATE TYPE request for a multidimensional ARRAY type. The actions listed are in addition to the dictionary updates that normally occur for a CREATE TYPE request.
    • The row that is already inserted into DBC.UDTInfo for a CREATE TYPE request also populates the columns DBC.UDTInfo.ArrayNumDimensions and DBC.UDTInfo.ArrayScope.

      See Teradata Vantage™ - Data Dictionary, B035-1092 for details about the values inserted in this row for a multidimensional ARRAY type.

    • One row is inserted into DBC.TVFields to record information about the element type specified for the multidimensional ARRAY type. The field name for the element type is recorded as the predefined value _ARRAYELEMENT with a field ID of 1025. If the element type specified for the multidimensional ARRAY type is a UDT, then the name of the UDT element type and its TypeID are also recorded.

      See Teradata Vantage™ - Data Dictionary, B035-1092 for details about the values inserted in this row for a multidimensional ARRAY type.