Alter Partitioning - Teradata VantageCloud Lake

PARTITION BY

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.

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 be eligible to be implicitly cast to INTEGER type, if 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
• 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 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.

PARTITION BY COLUMN

Column partitioning. 
You can define a large variety of partition levels with a large range in the number of combined partitions. However, complex partitioning can have greater impact on performance and storage. 
If you do not specify COLUMN or ROW for a column partition, the format is system-determined. For information about COLUMN format and ROW format, see Column-Partitioned Tables.

AUTO COMPRESS

Vantage determines and applies the best available compression method, if that method can reduce the size of physical rows. AUTO COMPRESS is the default for column partitions.

(COLUMN (column_name, column_name))

Indicates COLUMN format for the column partition. Column grouping provides flexibility in specifying which columns are grouped into which partitions while still being able to specify the display order in the table element list. Column grouping in the column list for a table allows for a simpler, but less flexible, specification of column groupings than you can specify in a partitioning level.

ROW (column_name, column_name))

Indicates ROW format for the column partition. ROW specifies that the column partition has ROW format. A ROW format means that only one column-partition value is stored in a physical row as a subrow.

WITH DELETE

Delete any row for which a new partitioning expression evaluates to a value outside the valid range of 1 through the number of partitions defined for that level.


PARTITION BY partitioning_level WITH DELETE has no effect, because the table must be empty. This option can be useful if the table is not empty and DROP RANGE is specified.

WITH INSERT

Insert into save_table any row for which a new partitioning expression evaluates to a value outside the valid range of 1 through the number of partitions defined for that level. After inserting the nonvalid row, Vantage deletes the row from the table.

PARTITION BY partitioning level WITH INSERT has no effect, because the table must be empty.

This option can be useful if the table is not empty and DROP RANGE is specified.

You can use this option with row-level security-protected tables if the tables referenced in the request are row-level security-protected and defined with the same row-level security constraints.
 If you do not specify the constraint values to be inserted into the target table, Vantage takes the constraint values for the target table from the source table.

INTO

Optional keyword preceding save_table.

save_table

save_table and the table being altered must be different tables, and save_table must have the same number of columns (with matching data types) as the table being modified.

NOT PARTITIONED

The table is not partitioned.

To change a partitioned table to a nonpartitioned table, specify NOT PARTITIONED.

DROP RANGE

Drop a set of ranges from the RANGE_N function on which a partitioning level for the table is based. You must specify the ranges to be dropped in ascending order. Use a conditional expression to drop a range set from the RANGE_N function on which a partitioning level for the table is based. You cannot DROP a condition from a CASE_N function in a partitioning. You must use PARTITION BY to redefine the entire partitioning for the table. For information, see RANGE_N.

DROP RANGE#L n

Represents a partition level number where n is an integer between 1 and 15, inclusive, for 2-byte partitioning and between 1 and 62, inclusive for 8-byte partitioning. You can only drop ranges from a character partition if the partitioning level for the table is derived exclusively from a RANGE_N function. You can only drop standard ranges from non-character partitioning levels. The term standard range refers to any range other than NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN. For example, a standard range can be a multilevel character partitioning with one or more non-character partitioning levels. You can only drop ranges in a character partitioning if the session collation matches the table collation and the session mode is the same as the session mode in effect when the table was created. You can only alter character partitioning levels that are defined using a RANGE_N function. You can only alter such character partitioning levels to add or drop a NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN partition. You can only drop standard ranges from non-character partitioning levels. For example, a multilevel character partitioning with one or more non-character partitioning levels. You can only drop ranges from a character partitioning if the session collation matches the table collation and the session mode is the same as the session mode in effect when the table was created. You can only alter character partitioning levels that are defined using RANGE_N functions, and you can only alter such partitions to add or drop a NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN partition.

BETWEEN start_expression

The start_expression must be defined according to the rules for the RANGE_N function. 
#L n represents a partition level number where n is an integer between 1 and 15, inclusive, for 2-byte partitioning and between 1 and 62, inclusive for 8-byte partitioning.

AND end_expression

Required only for the last range. The end_expression must be defined according to the rules for the RANGE_N function.

EACH range_size

A range with an EACH clause is equivalent to a series of start_expressions by adding multiples from 0 in increments of 1 of range_size to the specified start_expression. A range_size must be less than or equal to end_expression or less than the next start_expression. 
Each range_size must be a constant expression.

NO RANGE

UNKNOWN

You can drop NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN specifications from the definition for a RANGE_N function.

WHERE conditional_expression

You can specify a range expression or a conditional partition expression. For examples of dropping ranges based on a range expression or a conditional partition expression, see Example: Dropping and Adding Partition Ranges. You must base conditional_expression on the system-derived PARTITION column or PARTITION#L n column, where n ranges from 1 through 62, and depends on the level being modified and whether one or multiple levels are changed.

ADD RANGE


Add a set of ranges to the RANGE_N function on which a partitioning level for the table is based. You must specify the ranges to be added in ascending order. For more information, see RANGE_N.

ADD RANGE#L n

Partition level number where n is an integer value from 1 through 15, for 2-byte partitioning, or from 1 through 62, for 8-byte partitioning. There is no space between RANGE and #L n in a RANGE#L n specification. You can only add ranges if a partitioning level for the table is derived exclusively from RANGE_N functions. Use a RANGE_N function to define a mapping of ranges of the following types to INTEGER or BIGINT numbers:

• BIGINT
• BYTEINT
• CHARACTER
• DATE
• GRAPHIC
• INTEGER
• SMALLINT
• VARCHAR
• VARGRAPHIC

You can only add standard ranges from non-character partitioning levels. The term standard range here refers to any range other than NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN. For example, a multilevel character partitioning with one or more non-character partitioning levels.

You can only add ranges to a character partitioning if the session collation matches the table collation and the session mode matches the session mode in effect when the table was created.

You can only alter character partitioning levels that are defined using a RANGE_N function, and you can only alter such partitions to add or drop a NO RANGE, UNKNOWN, or NO RANGE OR UNKNOWN partition.

The maximum number of ranges, not including the NO RANGE, NO RANGE OR UNKNOWN, and UNKNOWN partitions for a RANGE_N partitioning level, is 9,223,372,036,854,775,805.

BETWEEN start_expression

The start_expression is defined according to the rules for the RANGE_N function..

AND end_expression

Required only for the last range.

The end_expression is defined according to the rules for the RANGE_N function. .

EACH range_size

Equivalent to a series of start_expressions by adding multiples from 0 in increments of 1 of range_size to the specified start_expression. A range_size must be less than or equal to end_expression or less than the next start_expression. The range_size variable must be a constant expression.

You cannot specify an EACH clause if the RANGE_N function specifies a character or graphic test value.

NO RANGE

UNKNOWN

You can add NO RANGE or UNKNOWN to the definition for a RANGE_N function.

WHERE conditional_expression

You can specify a range expression or a conditional partition expression. For examples of adding ranges based on a range expression or a conditional partition expression, see Example: Dropping and Adding Partition Ranges. You must base conditional_expression on the system-derived PARTITION column or PARTITION#L n column, where n ranges from 1 through 62, and depends on the level being modified and whether one or multiple levels are changed.

WITH DELETE

Delete any row for which a new partitioning expression evaluates to a value outside the valid range of 1 through the number of partitions defined for that level.

WITH INSERT

Insert into save_table any row for which a new partitioning level evaluates to a value outside the valid range of 1 through the number of partitions defined for that level. After inserting the nonvalid row, Vantage deletes the row from the table.

You can use this option with row-level security-protected tables if the tables referenced in the request are row-level security-protected and defined with the same row-level security constraints.

INTO

Optional keyword.

save_table

save_table and the table being altered must be different tables, and save_table must have the same number of columns (with matching data types) as the table being modified.