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.