Database Limits | Teradata Vantage - 17.10 - Database Limits - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

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

The database specifications in the following tables apply to a single database. The values presented are for their respective parameters individually and not in combination.

Name and Title Size Limits

Parameter Value
Maximum name size for database objects, for example, account, attribute, authorization, column, constraint, database, function, GLOP, index, macro, method, parameter, password, plan directive, procedure, profile, proxy user, query, role, procedure, table, transform group, trigger, UDF, UDM, UDT, using variable name, view.

See Teradata Vantage™ - SQL Fundamentals, B035-1141 for other applicable naming rules.

128 UNICODE characters
Maximum system name size.

Used in SQL statements for target level emulation. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

63 characters
Maximum SQL text title size. 256 characters

User Limits

Parameter Value
Maximum number of external roles. 50

Table and View Limits

Parameter Value
Maximum number of journal tables per database. 1
Maximum number of error tables per base data table. 1
Maximum number of columns per base data table or view. 2,048
Maximum number of columns per error table.

This limit includes 2,048 data table columns plus 13 error table columns.

2,061
Maximum number of UDT columns per base data table.

The same limit is true for both distinct and structured UDTs.

The absolute limit is 2,048, and the realizable number varies as a function of the number of other features declared for a table that occupy table header space.

~1,600
Maximum number of LOB and XML columns per base data table.

This limit includes predefined type LOB columns and UDT LOB columns.

A LOB UDT or XML UDT column counts as one column even if the UDT is a structured type that has multiple LOB attributes.

32
Maximum number of columns created over the life of a base data table. 2,560
Maximum number of rows per base data table. Limited only by disk capacity.
Maximum number of bytes per table header per AMP.

A table header that is large enough to require more than ~64,000 bytes uses multiple 64KB rows per AMP up to 1 MB.

A table header that requires 64,000 or fewer bytes uses only a single row and does not use the additional rows that are required to contain a larger table header.

The maximum size for a table header is 1 MB.

1 MB
Maximum number of characters per SQL index constraint. 16,000
Maximum non-spool row size. 1 MB
Maximum internal spool row size. ~ 1MB
Maximum size of the queue table FIFO runtime cache per table. 2,211 row entries
Maximum logical row size.

In this case, a logical row is defined as a base table row plus the sum of the bytes stored in a LOB or XML subtable for that row.

67,106,816,000 bytes
Maximum non-LOB column size for a nonpartitioned table.

This limit is based on subtracting the minimum row overhead value for a nonpartitioned table row (12 bytes) from the system-defined maximum row length (64,256 bytes).

64,244 bytes
Maximum non-LOB column size for a partitioned table.

This limit is based on subtracting the minimum row overhead value for a partitioned table row (16 bytes) from the system-defined maximum row length (64,256 bytes).

64,240 bytes
Maximum number of values (excluding NULLs) that can be multivalue compressed per base table column. 255
Maximum amount of BYTE data per column that can be multivalue compressed. 4,093 bytes
Maximum amount of data per column that can be multivalue compressed for GRAPHIC, LATIN, KanjiSJIS, and UNICODE server character sets. 8,188 characters
Maximum number of columns that can be compressed per primary-indexed or primary-AMP-indexed table or join index using multivalue compression or algorithmic compression.

This assumes that the object is not a NoPI table or join index or a global temporary trace table. All other tables, hash indexes, and join indexes must have a primary index or a primary AMP index. Primary indexes and primary AMP indexes cannot be either multivalue compressed or algorithmically compressed. Because of this, the limit is the maximum number of columns that can be defined for a table, which is 2,048, minus 1.

The limit for multivalue compression is far more likely to be reached because of table header overflow, but the amount of table header space that is available for multivalue compressed values is limited by a number of different factors.

Join index columns inherit their compression characteristics from their parent tables.

2,047
Maximum number of columns that can be compressed per NoPI table using multivalue compression or algorithmic compression Column-partitioned NoPI join index columns inherit their compression characteristics from their parent tables. 2,048
Maximum number of algorithmically compressed values per base table column. Unlimited
Maximum width of data that can be multivalue compressed for BYTE, BYTEINT, CHARACTER, GRAPHIC, VARCHAR, and VARGRAPHIC data types. Unlimited
Maximum width of data that can be multivalue compressed for data types other than BYTE, BYTEINT, CHARACTER, DATE, GRAPHIC, VARCHAR, and VARGRAPHIC. Unlimited
Maximum width of data that can be algorithmically compressed.

The maximum data width is unlimited if you specify only algorithmic compression for a column.

If you specify a mix of multivalue and algorithmic compression for a column, then the limits for multivalue compression also apply for algorithmic compression.

Unlimited
Maximum number of table-level CHECK constraints that can be defined per table. 100
Maximum number of primary indexes or primary AMP indexes per table, hash index, or join index that is not a NoPI database object. 1
Minimum number of primary indexes per primary-indexed table, hash index, or join index. 1
Minimum number of primary AMP indexes per primary-AMP-indexed table or join index. 1
Maximum number of primary indexes per primary-AMP-indexed or NoPI or join index or global temporary trace table. 0
Maximum number of columns per primary index or primary AMP index. 64
Maximum number of column partitions per table, including two columns partitions reserved for internal use). 2,050
Minimum number of column partition numbers that must be available for use by an ALTER TABLE request to alter a column partition. 1
Maximum partition number for a column partitioning level. Maximum number of column partitions for that level + 1 Maximum number of column partitions for that level + 1
Maximum combined partition number for a single-level column-partitioned table or column-partitioned join index. The same as the maximum partition number for the single partitioning level.
Maximum number of rows per hash bucket for a 44-bit uniqueness value. 17,592,186,044,415
Maximum combined partition number for a multilevel partitioning for 2-byte partitioning. 65,535
Maximum combined partition number for a multilevel partitioning join index for 8-byte partitioning. 9,223,372,036,854,775,807
Maximum number of ranges, excluding the NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN and UNKNOWN partitions, for a RANGE_N partitioning expression for 2-byte partitioning.

This value is limited by the largest possible INTEGER value.

65,533
Maximum number of ranges, excluding the NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN and UNKNOWN partitions, for a RANGE_N partitioning expression for 8-byte partitioning.

This value is limited by the largest possible BIGINT value.

9,223,372,036,854,775,805
Minimum value for n in a RANGE#L n expression. 1
Minimum value for n in a RANGE#L n expression for 2-byte partitioning. 15
Minimum value for n in a RANGE#L n expression for 8-byte partitioning. 62
Maximum number of partitions, including the NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN partitions, for a single-level partitioning expression composed of a single RANGE_N function with INTEGER data type. 2.147.483.647
Maximum number of ranges for a single-level partitioning expression composed of a single RANGE_N function with INTEGER data type that is used as a partitioning expression if the NO RANGE and UNKNOWN partitions are not specified. 65,533
Maximum number of ranges for a single-level partitioning expression composed of a single RANGE_N function with BIGINT data type that is used as a partitioning expression if the NO RANGE and UNKNOWN partitions are not specified. 9,223,372,036,854,775,805
Maximum number of ranges for a single-level partitioning expression composed of a single RANGE_N function with BIGINT data type that is used as a partitioning expression if both the NO RANGE and UNKNOWN partitions are specified. 9,223,372,036,854,775,807
Maximum value for a partitioning expression that is not based on a RANGE_N or CASE_N function.

This is allowed only for single-level partitioning.

65,535
Maximum number of defined partitions for a column partitioning level. The number of column partitions specified + 2.

The 2 additional partitions are reserved for internal use.

Maximum number of defined partitions for a row partitioning level if the row partitions specify the RANGE_N or CASE_N function. The number of row partitions specified.
Maximum number of defined partitions for a row partitioning level if the row partitions do not specify the RANGE_N or CASE_N function. 65,535
Maximum number of partitions for a partitioning level when you specify an ADD clause.

This value is computed by adding the number of defined partitions for the level plus the value of the integer constant specified in the ADD clause.

9,223,372,036,854,775,807
Maximum number of partitions for a column partitioning level when you do not specify an ADD clause and at least one row partitioning level does not specify an ADD clause. The number of column partitions defined + 10.
Maximum number of column partitions for a column partitioning level when you do not specify an ADD clause, you also specify row partitioning, and each of the row partitions specifies an ADD clause. The largest number for the column partitioning level that does not cause the partitioning to be 8-byte partitioning.
Maximum number of partitions for each row partitioning level without an ADD clause in level order, if using the number of row partitions defined as the maximum for this and any lower row partitioning level without an ADD clause. The largest number for the column partitioning level that does not cause the partitioning to be 8-byte partitioning.
Maximum partition number for a row-partitioning level. The same as the maximum number of partitions for the level.
Minimum number of partitions for a row-partitioning level. 2
Maximum number of partitions for a CASE_N partitioning expression.

This value is limited by the largest possible INTEGER value.

2,147,483,647
Maximum value for a RANGE_N function with an INTEGER data type. 2,147,483,647
Maximum value for a RANGE_N function with a BIGINT data type that is part of a partitioning expression. 9,223,372,036,854,775,805
Maximum value for a CASE_N function for both 2-byte and 8-byte partitioning. 2,147,483,647
Maximum number of partitioning levels for 2-byte partitioning.

Other limits can further restrict the number of levels for a specific partitioning.

15
Maximum number of partitioning levels for 8-byte partitioning.

Other limits can further restrict the number of levels for a specific partitioning.

62
Maximum value for n for the system-derived column PARTITION#L n. 62
Minimum number of partitions per row-partitioning level for a multilevel partitioning primary index. 1
Minimum number of partitions defined for a row-partitioning level. 2 or greater
Maximum number of partition number ranges from each level that are not eliminated for static row partition elimination for an 8-byte row-partitioned table or join index. 8,000
Maximum number of table-level constraints per base data table. 100
Maximum size of the SQL text for a table-level index CHECK constraint definition. 16,000 characters
Maximum number of referential integrity constraints per base data table. 64
Maximum number of columns per foreign and parent keys in a referential integrity relationship. 64
Maximum number of characters per string constant. 31,000
Maximum number of row-level security constraints per table, user, or profile. 5
Maximum number of row-level security statement-action UDFs that can be defined per table. 4
Maximum number of non-set row-level security constraint encodings that can be defined per constraint.

The valid range is from 1 to 10,000.

0 is not a valid non-set constraint encoding.

10,000
Maximum number of set row-level security constraint encodings that can be defined per constraint. 256

Spool Space Limits

Parameter Value
Maximum internal spool row size. ~ 1MB

BLOB, CLOB, XML, and Related Limits

Parameter Value
Maximum BLOB object size 2,097,088,000 8-bit bytes
Maximum CLOB object size
  • 2,097,088,000 single-byte characters
  • 1,048,544,000 double-byte characters
Maximum XML object size 2,097,088,000 8-bit bytes
Maximum number of LOB rows per rowkey per AMP for NoPI LOB or XML tables ~ 256M

The exact number is 268,435,455 LOB or XML rows per rowkey per AMP.

Maximum size of the file name passed to the AS DEFERRED BY NAME option in a USING request modifier VARCHAR(1024)

User-Defined Data Type, ARRAY Data Type, and VARRAY Data Type Limits

Parameter Value
Maximum structured UDT size.

This value is based on a table having a 1 byte (BYTEINT) primary index. Because a UDT column cannot be part of any index definition, there must be at least one non-UDT column in the table for its primary index.

Row header overhead consumes 14 bytes in an NPPI table and 16 bytes in a PPI table, so the maximum structured UDT size is derived by subtracting 15 bytes (for an NPPI table) or 17 bytes (for a PPI table) from the row maximum of 64,256 bytes.

  • 64,242 bytes (NoPI table)
  • 64,241 bytes (NPPI table)
  • 64,239 bytes (PPI table)
Maximum number of UDT columns per base data table.

The absolute limit is 2,048, and the realizable number varies as a function of the number of other features declared for a table that occupy table header space.

The figure of 1,600 UDT columns assumes a FAT table header.

This limit is true whether the UDT is a distinct or a structured type.

~1,600
Maximum database, user, base table, view, macro, index, trigger, procedure, UDF, UDM, UDT, constraint, or column name size.

Other rules apply for Japanese character sets, which might restrict names to fewer than 30 bytes. See Teradata Vantage™ - SQL Fundamentals, B035-1141 for the applicable rules.

30 bytes in Latin or Kanji1 internal representation
Maximum number of attributes that can be specified for a structured UDT per CREATE TYPE or ALTER TYPE request.

The maximum is platform-dependent, not absolute.

300 - 512
Maximum number of attributes that can be defined for a structured UDT.

While you can specify no more than 300 to 512 attributes for a structured UDT per CREATE TYPE or ALTER TYPE request, you can submit any number of ALTER TYPE requests with the ADD ATTRIBUTE option specified as necessary to add additional attributes to the type up to the upper limit of approximately 4,000.

~4,000
Maximum number of levels of nesting of attributes that can be specified for a structured UDT. 512
Maximum number of methods associated with a UDT.

There is no absolute limit on the number of methods that can be associated with a given UDT.

Methods can have a variable number of parameters, and the number of parameters directly affects the limit, which is due to Parser memory restrictions.

There is a workaround for this issue. See ALTER TYPE information in Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184 for details.

~500
Maximum number of input parameters with a UDT data type of VARIANT_TYPE that can be declared for a UDF definition. 8
Minimum number of dimensions that can be specified for a multidimensional ARRAY or VARRAY data type. 2
Maximum number of dimensions that can be specified for a multidimensional ARRAY or VARRAY data type. 5

Macro, UDF, SQL Procedure, and External Routine Limits

Parameter Value
Maximum number of parameters specified in a macro. 2,048
Maximum expanded text size for macros and views. 2 MB
Maximum number of open cursors per procedure. 15
Maximum number of result sets a procedure can return. 15
Maximum number of columns returned by a dynamic result table function.

The valid range is from 1 to 2,048. There is no default.

2,048
Maximum number of dynamic SQL requests per procedure. 15
Maximum length of a dynamic SQL request in a procedure.

This includes its SQL text, the USING data (if any), and the CLIv2 parcel overhead.

Approximately 1 MB
Maximum combined size of the parameters for a procedure 1 MB for input parameters

1 MB for output (and input/output) parameters

Maximum size of condition names and UDF names specified in a procedure. 30 bytes
Maximum number of parameters specified in a UDF defined without dynamic UDT parameters. 128
Maximum number of parameters that can be defined for a constructor method for all types except ARRAY/VARRAY 128
Maximum number of parameters that can be defined for a constructor method of an ARRAY/VARRAY type n

where n is the number of elements defined for the type.

Maximum number of combined return values and local variables that can be declared in a single UDF. Unlimited
Maximum number of combined external routine return values and local variables that can be instantiated at the same time per session. 1,000
Maximum combined size of the parameters defined for a UDF. 1 MB for input parameters

1 MB for output parameters

Maximum number of parameters specified in a UDF defined with dynamic UDT parameters.

The valid range is from 0 to 15. The default is 0.

1,144
Maximum number of parameters specified in a method. 128
Maximum number of parameters specified in an SQL procedure. 256
Maximum number of parameters specified in an external procedure written in C or C++. 256
Maximum number of parameters specified in an external procedure written in Java. 255
Maximum size of an ARRAY or VARRAY UDT.

This limit does not include the number of bytes used by the row header and the primary index or primary AMP index of a table.

64 KB
Maximum length of external name string for an external routine.

An external routine is the portion of a UDF, external procedure, or method that is written in C, C++, or Java (only external procedures can be written in Java). This is the code that defines the semantics for the UDF, procedure, or method.

1,000 characters
Maximum package path length for an external routine. 256 characters
Maximum SQL text size in a procedure. Approximately 1 MB
Maximum number of nested CALL statements in a procedure. 15
Maximum number of Statement Areas per SQL procedure diagnostics area. See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 and Teradata Vantage™ - SQL External Routine Programming, B035-1147. 1
Maximum number of Condition Areas per SQL procedure diagnostics area. See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 and Teradata Vantage™ - SQL External Routine Programming, B035-1147. 16

Query and Workload Analysis Limits

Parameter Value
Maximum number of columns and indexes on which statistics can be collected or recollected at one time.

512 or limited by available parse tree memory and amount of spool.

512
Maximum number of pseudo indexes on which multicolumn statistics can be collected and maintained at one time.

A pseudo index is a file structure that allows you to collect statistics on a composite, or multicolumn, column set in the same way you collect statistics on a composite index.

This limit is independent of the number of indexes on which statistics can be collected and maintained.

32
Maximum number of sets of multicolumn statistics that can be collected on a table or join index if single-column PARTITION statistics are not collected on the table or index. 32
Maximum number of sets of multicolumn statistics that can be collected on a table or join index if single-column PARTITION statistics are collected on the table or index. 31

Secondary, Hash, and Join Index Limits

Parameter Value
Number of tables that can be referenced in a join. 128
Minimum number of secondary, hash, and join indexes, in any combination, per base data table. 0
Maximum number of secondary, hash, and join indexes, in any combination, per base data table.

Each composite NUSI defined with an ORDER BY clause counts as 2 consecutive indexes in this calculation.

The number of system-defined secondary and single-table join indexes contributed by PRIMARY KEY and UNIQUE constraints counts against the combined limit of 32 secondary, hash, and join indexes per base data table.

32
Maximum number of columns referenced per secondary index. 64
Maximum number of columns referenced per single table in a hash or join index. 64
Maximum number of rows per secondary, hash, or join index.

Limited only by disk capacity.

Maximum number of columns referenced in the fixed part of a compressed join index.

Vantage implements a variety of different types of user-visible compression in the system.

When describing compression of hash and join indexes, compression refers to a logical row compression in which multiple sets of nonrepeating column values are appended to a single set of repeating column values. This allows the system to store the repeating value set only once, while any nonrepeating column values are stored as logical segmental extensions of the base repeating set.

64
Maximum number of columns referenced in the repeating part of a compressed join index. 64
Maximum number of columns in an uncompressed join index. 2,048
Maximum number of columns in a compressed join index.
  • 64 for repeating
  • 64 for nonrepeating

Reference Index Limits

Parameter Value
Maximum number of reference indexes per base data table.

There is a maximum of 128 Reference Indexes in a table header, 64 from a parent table to child tables and 64 from child tables to a parent table.

64

SQL Request and Response Limits

Parameter Value
Maximum SQL text size per request.

This includes SQL request text, USING data, and parcel overhead.

1 MB
Maximum request message size. The message includes SQL request text, USING data, and parcel overhead. 7 MB
Maximum number of entries in an IN list.

There is no fixed limit on the number of entries in an IN list; however, other limits such as the maximum SQL text size, place a request-specific upper bound on this number.

Unlimited
Maximum SQL activity count size. 8 bytes
Maximum number of tables and single-table views that can be joined per query block.

This limit is controlled by the MaxJoinTables DBS Control field.

128
Maximum number of partitions for a hash join operation. 50
Maximum number of subquery nesting levels per query. 64
Maximum number of tables or single-table views that can be referenced per subquery.

This limit is controlled by the MaxJoinTables DBS Control field..

128

Maximum number of fields in a USING row descriptor. 2,536
Maximum number of bytes in USING data. This value does not include Smart LOB (SLOB) data. 1,040,000
Maximum number of open cursors per embedded SQL program. 16
Maximum SQL text response size. 1 MB
Maximum number of columns per DML request ORDER BY clause. 64
Maximum number of columns per DML request GROUP BY clause. 64
Maximum number of fields in a CONSTANT row. 32,768

Row-Level Security Constraint Limits

Parameter Value
Maximum number of row-level security constraints per table. 5
Maximum number of hierarchical row-level security constraints per user or profile. 6
Maximum number of values per hierarchical row-level security constraint. 10,000
Maximum number of non-hierarchical row-level security constraints per user or profile. 2
Maximum number of values per non-hierarchical row-level security constraint. 256