15.10 - PARTITION BY - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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:

  • UDT
  • ARRAY/VARRAY
  • BLOB
  • CLOB
  • JSON
  • XML
  • Geospatial
  • Period

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