17.00 - PARTITION BY - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
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.

partitioning_expression
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
  • ARRAY/VARRAY
  • UDT
  • Period
  • XML
  • Geospatial
  • JSON
  • DATASET

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 .

COLUMN
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.