15.00 - Table and View Limits - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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.

64,256 bytes

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.

This value is derived by multiplying the maximum number of LOB or XML columns per base table (32) times the maximum size of a LOB or XML column, both of which are 2,097,088,000 8-bit bytes.

Remember that each LOB or XML column consumes 40 bytes of Object ID from the base table, so 1,248 of those 67,106,816,000 bytes cannot be used for data.

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 table using multivalue compression or algorithmic compression.

This assumes that the object is not a non‑partitioned NoPI table, a column‑partitioned table or join index, or a global temporary trace table. All other tables, hash indexes, and join indexes must have a primary index, and primary 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. See Database Design for details.

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

2,047

Maximum number of columns that can be compressed per nonpartitioned NoPI table or column‑partitioned table using multi‑value compression or algorithmic compression

Column‑partitioned 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 per table, hash index, or join index that is not a NoPI or column‑partitioned database object.

1

Minimum number of primary indexes per primary‑indexed table, hash index, or join index.

1

Maximum number of primary indexes per non‑partitioned NoPI table, column‑partitioned table or join index, or global temporary trace table.

This maximum applies only to nonpartitioned NoPI tables, column‑partitioned tables and join indexes, and global temporary trace tables. All other tables, hash indexes, and join indexes must have a primary index.

0

Maximum number of columns per primary 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 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, including 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, including 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#Ln expression.

1

Maximum value for n in a RANGE#Ln expression for 2‑byte partitioning

15

Maximum value for n in a RANGE#Ln 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#Ln.

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