Limitations Related to Partitioned Tables | CREATE TABLE | Teradata Vantage - 17.10 - Memory Limitations Related to Partitioned Tables - 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)

You might encounter several different memory limitations when working with partitioned tables. The following table addresses some of these limitations by documenting the relevant error message numbers and providing explanations or workarounds for them.

Message Number Problem Possible Cause Remedy
3708 Table header size limit exceeded (see Teradata Vantage™ - Database Design, B035-1094).

The limit is 64 KB for a thin table header and 1 MB for a fat table header.

Too many columns in the table. If possible, reduce the number of columns in the table.
Too many distinct values compressed in the table. If possible, reduce the number of compressed values.
Partitioning expressions are too complex. Simplify the partitioning expressions or reduce their number.

If possible, use RANGE_N with EACH clauses instead of CASE_N.

3710 Parser memory size limit exceeded.

The limit is variable and is determined by the value of the MaxParseTreeSegs parameter in DBS Control.

Current values for DBS Control parameters do not allocate enough parser memory to process the request. Change the values for the following DBS Control parameters to the specified settings.

You might find that your query workloads that require these values need to be increased still further.

See Teradata Vantage™ - Database Utilities, B035-1102 for information about how to change the value for MaxParseTreeSegs in the DBS Control record.
  • If the problem occurs with PPI tables that do not have hash or join indexes, then change the value for MaxParseTreeSegs to this value.

    2,000 for byte-packed systems.

    4,000 for byte-aligned systems.

  • If the problem occurs with PPI tables that have hash or join indexes, then change the value for MaxParseTreeSegs to this value and contact Teradata Support.

    2,000 for byte-packed systems.

    4,000 for byte-aligned systems.

Partitioning expressions are too complex. Simplify the partitioning expressions or reduce their number.

If possible, use RANGE_N with EACH clauses instead of CASE_N.

3891 Check if the partitioning CHECK constraint text size limit is exceeded.

The partitioning CHECK constraint text is derived from the partitioning expression for the table or join index.

Text for the partitioning expressions in the PPI definition is too long. Reduce the partitioning constraint text to 16,000 or fewer characters.

30 characters of the 16,000 character limit apply to the following constraint for single-level partitioning: CHECK (partitioning_expression) BETWEEN 1 AND 65,535. This defines the limit for the partitioning expression to be 16,000 - 30 = 15,970 characters.

3930 The dictionary cache is full. Dictionary cache is too small. Increase size of dictionary cache to 1 MB.

Because the default size of the dictionary cache is 1 MB, the only reason you might need to do this is if the DBA has reduced the size of the dictionary cache to something less than its default.

See Messages for additional information about these error messages.

The PPICacheThrP parameter of the DBS Control utility (for more information see Teradata Vantage™ - Database Utilities, B035-1102, Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142, and Teradata Vantage™ - Database Design, B035-1094) specifies the percentage value the system uses to calculate the cache threshold used in operations dealing with multiple partitions. The value is specified in units of 0.10 percent and can range between 0 and 500, inclusive.

PPICacheThrP also specifies the percentage value to use for calculating the number of memory segments that can be allocated to buffer the appending of column partition values to column partitions for column-partitioned tables.

The sum of the sizes of this memory minus some overhead divided by the size of a column partition context determines the number of available column partition contexts. If there are more column partitions in a target column-partitioned table than available column partition contexts, multiple passes over the source rows are required to process a set of column partitions where the number of column partitions in each set equals the number of available column partition contexts. In this case, there is only one file context open, but each column partition context allocates buffers in memory.

The Optimizer also uses the DBS Control parameter PPICacheThrP to determine the number of available file contexts that can be used at a time to access a partitioned table.

IF PPICacheThrP determines the number of available file contexts to be … THEN Vantage considers this many file contexts to be available …
< 8 8
> 256 256

Vantage uses the number of file contexts as the number of available column partition contexts for a column-partitioned table.

Vantage might associate a file context with each column partition context for some operations, and in other cases it might allocate a buffer with each column partition context.

Ideally, the number of column partition contexts should be at least equal to the number of column partitions that need to be accessed by a request. Otherwise, performance can degrade because not all of the needed column partitions can be read at one time.

Performance and memory usage can be impacted if PPICacheThrP is set too high, which can lead to memory thrashing or a system crash. At the same time, the benefits of partitioning can be lessened if the value for the DBS Control parameter PPICacheThrP is set unnecessarily low, causing performance to degrade significantly.

The default is expected to be applicable to most workloads, but you might need to adjust for the best balance.

The PPI Cache Threshold (PCT) is defined as follows for byte-packed format systems and byte-aligned format systems with file system cache per AMP values less than 100 MB.


PCT equation (file system cache < 100 MB)

PCT is defined as follows for byte-aligned format systems with file system cache per AMP values greater than 100 MB.


PCT equation (file system cache > 100 MB)

The default is 10, which represents 1.0 percent. Never change this value without first performing a thorough analysis of the impact of the change on your query workloads.

PPICacheThrP controls the memory usage of operations on partitioned tables. Larger values improve the performance of these operations, with the following exceptions:
  • Data blocks are kept memory-resident.

    If they must be swapped to disk, performance might begin to degrade.

  • The number of contexts does not exceed the number of populated, non-eliminated partitions being processed.

    In this case, increasing the value of PPICacheThrP does not improve performance because each partition has a context, and additional contexts would not be used.

For byte-aligned format systems only, the maximum partitioning cache that can be allocated is 100 MB regardless of the setting for PPICacheThrP.
The PCT value is used for the following operations on partitioned tables:
  • Joins on a partitioned table.
  • Aggregation of a partitioned table.

You should decrease PPICacheThrP if memory contention occurs during these types of operations.

Joins on a Partitioned Table

If a join is performed on a partitioned table or spool and the other table or spool is not partitioned, the maximum number of partitions processed at one time from the partitioned table or spool is equal to the following calculation.


Max. number of partitions processed (join performed)

If the join is made on the primary index of 2 partitioned relations, then the maximum number of partitions processed at a time from the relations is calculated as follows.


Max. number of partitions processed (join performed)

where:


""

For each partition that is being processed, 1 data block is always memory-resident.

The definitions for the variables in this equation are as follows.

Equation element … Specifies the …
f1 number of partitions to be processed at one time from the left relation in the join as determined by the Optimizer.
f2 number of partitions to be processed at one time from the right relation in the join as determined by the Optimizer.
db1 estimated average datablock size of the left relation in the join.
db2 estimated average datablock size of the right relation in the join.

Aggregation of a Partitioned Table

If the system aggregates on the primary index of a partitioned table, the maximum number of partitions processed at one time from the table is calculated as follows.


Max. number of partitions processed (aggregation)

For each partition that is being processed, 1 data block is always memory-resident.