17.10 - Using CASE_N to Define Partitioned Primary Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

The primary index for a table or join index controls the distribution and retrieval of the data for that table or join index across the AMPs. If the primary index is a partitioned primary index (PPI), the data can be assigned to user-defined partitions on the AMPs.

To define a primary index for a table or join index, you specify the PRIMARY INDEX phrase in the CREATE TABLE or CREATE JOIN INDEX data definition statement. To define a partitioned primary index, you include the PARTITION BY phrase when you define the primary index.

The PARTITION BY phrase requires one or more partitioning expressions that determine the partition assignment of a row. You can use CASE_N to construct a partitioning expression such that a row with any value or NULL for the partitioning columns is assigned to some partition.

You can also use RANGE_N to construct a partitioning expression. For more information, see “RANGE_N”.

If the PARTITION BY phrase specifies a list of partitioning expressions, the PPI is a multilevel PPI, where each partition for a level is subpartitioned according to the next partitioning expression in the list. Unlike the partitioning expression for a single-level PPI, which can consist of any valid SQL expression (with some exceptions), each expression in the list of partitioning expressions for a multilevel PPI must be a CASE_N or RANGE_N function.

You cannot ADD or DROP partitioning expressions that are based on a CASE_N function. To modify a partitioning expression that is based on a CASE_N function, you must use the ALTER TABLE statement with the MODIFY PRIMARY INDEX option to redefine the entire PARTITION BY clause, and the table must be empty.

For more information, see “ALTER TABLE” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.