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.
|
| 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 Teradata Database considers this many file contexts to be available … |
|---|---|
| < 8 | 8 |
| > 256 | 256 |
Teradata Database uses the number of file contexts as the number of available column partition contexts for a column-partitioned table.
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 is defined as follows for byte-aligned format systems with file system cache per AMP values greater than 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.
- 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.
- 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.
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.
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.
For each partition that is being processed, 1 data block is always memory-resident.