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 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. 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 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 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#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 |