Primary indexes for global temporary, volatile, and standard base tables can be partitioned or nonpartitioned. Nonpartitioned NoPI tables, with the exception of column-partitioned tables, cannot be partitioned because they have no primary index to partition. See Column-Partitioned Tables.
An nonpartitioned primary index is the traditional primary index by which rows are assigned to AMPs. Apart from maintaining their storage in row hash order, no additional assignment processing of rows is performed once they are hashed to an AMP.
A partitioned primary index (PPI) permits rows to be assigned to user-defined data partitions on the AMPs, enabling enhanced performance for range queries that are predicated on primary index values. For details, see Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
You can define partitioning on a single level or on multiple levels. A PPI defined on multiple levels is referred to as a multilevel PPI, or MLPPI.
Depending on the number of combined partitions for a table, its partition numbers consume either 2 bytes or 8 bytes in the row header, as the following table specifies.
| IF a table has this many combined partitions … | Its partition number consumes this many bytes in the row header … |
|---|---|
| ≤ 65, 535 | 2 |
| > 65,535 | 8 |
Single-level column partitioning with no ADD option also consumes 2 bytes in the row header.
- Comparison of CHARACTER or CHARACTER SET GRAPHIC data involving columns or expressions using the Kanji1 or KanjiSJIS server character sets.
- Any kind of user-defined function
- Aggregate functions
- Ordered analytical functions
- Built-in functions
- The RANDOM function
- The HASHAMP and HASHBAKAMP functions
HASHROW and HASHBUCKET are permitted.
- The system-derived PARTITION and PARTITION#L n columns
- Set operators
- Subqueries
- Columns having a BLOB, CLOB, or Geospatial data type.
- Direct partitioning on a numeric column
- Expressions based on 1 or more columns
- Expressions based on the CASE_N function
- Expressions based on the RANGE_N function
See Teradata Vantage™ - Database Design, B035-1094 for details on the various usage considerations for each of these partitioning strategies.
- Expressions based on the CASE_N function (see Teradata Vantage™ - Database Design, B035-1094 for details).
- Expressions based on the RANGE_N function (see Teradata Vantage™ - Database Design, B035-1094 for details).
The following table presents the intended use of the CASE_N and RANGE_N functions for partitioning expressions.
| Use this function … | To define a mapping … |
|---|---|
| CASE_N | between conditions to INTEGER numbers. |
| RANGE_N | of ranges of INTEGER or DATE values to INTEGER numbers. |
The partitioning expressions you can define for partitioning a table have certain restrictions regarding the data types you can specify within them and with respect to the data type of the result of the function.
The following table summarizes these restrictions.
| Data Type | PARTITION BY | ||
|---|---|---|---|
| RANGE_N | CASE_N | Expression | |
| ARRAY VARRAY |
N | N | N |
| BIGINT | Y | X | I |
| BLOB | N | N | N |
| BYTE | X | X | X |
| BYTEINT | Y | X | I |
| CHARACTER | Y | X | I |
| CLOB | N | N | N |
| DATE | Y | X | I |
| DECIMAL NUMERIC |
X | X | I |
| NUMBER (exact form) | X | X | I |
| DOUBLE PRECISION FLOAT REAL |
X | X | I |
| NUMBER (approximate form) | X | X | I |
| GRAPHIC | N | X | N |
| INTEGER | Y | X | Y |
| INTERVAL YEAR | X | X | I |
| INTERVAL YEAR TO MONTH | X | X | X |
| INTERVAL MONTH | X | X | I |
| INTERVAL DAY | X | X | I |
| INTERVAL DAY TO HOUR | X | X | X |
| INTERVAL DAY TO SECOND | X | X | X |
| INTERVAL SECOND | X | X | X |
| LONG VARCHAR | Y | X | I |
| LONG VARCHAR CHARACTER SET GRAPHIC | N | N | N |
| PERIOD The BEGIN and END bound functions are valid in a partitioning expression when they are defined on a valid PERIOD column and the result can be cast implicitly to a numeric data type. |
N | X | N |
| SMALLINT | Y | X | I |
| TIME | X | X | X |
| TIME WITH TIME ZONE | X | X | X |
| TIMESTAMP | Y | X | X |
| TIMESTAMP WITH TIME ZONE | Y | X | X |
| UDT | N | N | N |
| VARBYTE | X | X | X |
| VARCHAR | Y | X | I |
| VARGRAPHIC | N | N | N |
| JSON | N | N | N |
| XML | N | N | N |
The following table explains the abbreviations used in the previous table.
| Key | |
|---|---|
| Symbol | Definition |
| I | Valid for a partitioning expression. If the type is also the data type of the result, then it must be such that it can be cast to a valid INTEGER value. |
| N | Not valid for a partitioning expression. If the partitioning expression is defined using a CASE_N function, then this type is not valid for the CASE_N condition. |
| X | Valid for a partitioning expression, but cannot be the data type of the result of the expression. If the partitioning expression is defined using a CASE_N function, then this type is valid for the CASE_N condition. |
| Y | Valid for a partitioning expression and valid as the data type of the result of the partitioning expression. |