17.00 - PARTITION BY - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
Release Date
September 2020
Content Type
Programming Reference
Publication ID
English (United States)

Add or change partitioning for a table. The table must be empty.

To change an nonpartitioned table to be partitioned or to change the partitioning for an existing partitioned table, specify PARTITION BY and then specify a valid partitioning. The partitioning is one or more partitioning expressions, a COLUMN specification, or a combination of both.

For 2-byte partitioning, you can specify a maximum of 15 partitioning levels.

For 8-byte partitioning, you can specify a maximum of 62 partitioning levels.

You cannot specify a row-level security constraint column as a partitioning column.

The result of a partitioning expression not based on a RANGE_N or CASE_N function must always be eligible to be implicitly cast to INTEGER type, if it is not already INTEGER value. A partitioning expression not based on a RANGE_N or CASE_N is only allowed if there is a single level of partitioning.

Use a CASE_N function to define a mapping between conditions to INTEGER numbers. The maximum number of partitions for a CASE_N partitioning level is limited by the maximum partitioning constraint text size, the request text size limit, and other factors.

You cannot alter a table to have a partitioning level that includes a row-level security constraint column.

A partitioning expression cannot specify external or SQL UDFs or columns with the following data types:

  • BLOB
  • CLOB
  • UDT
  • Period
  • XML
  • Geospatial
  • JSON

However, you can reference Period columns indirectly using the BEGIN and END bound functions. See Example: CASE_N Partitioning Expression Using the END Bound Function and Teradata Vantage™ - Temporal Table Support , B035-1182 .

A partitioning can only include a column partitioning level if the table does not have a primary index or ALTER TABLE specifies NO PRIMARY INDEX.