WITH INSERT and WITH DELETE Null Partition Handling Clauses - Advanced SQL Engine - Teradata Database

The following rules apply to the use of the WITH INSERT INTO save_table and WITH DELETE null partition handling clauses when modifying or reevaluating the row partitioning of a table or join index.

If You Do Not Specify a Null Partition Handling Clause

If you do not specify a WITH INSERT INTO save_table or WITH DELETE null partition handling clause, a new partitioning expression for all rows currently in the table must generate a value between 1 the number of partitions defined for that level, after the partitioning expression value is cast to INTEGER or BIGINT, if it is not already typed as INTEGER, BIGINT, or CHARACTER.

Otherwise, the system returns an error to the requester.

If an Existing Row Violates a New Partitioning Expression

If an existing row violates a new partitioning expression and you have not specified a WITH DELETE or WITH INSERT INTO save_table null partition handler clause, then you cannot apply the new partitioning expression.

Rows that violate the implied partitioning CHECK constraint, including those whose partitioning expression evaluates to null, are not allowed in the table.

WITH DELETE Null Partition Handling Clause

If you specify a WITH DELETE null partition handling clause, any row for which a new partitioning expression does not generate, after casting to INTEGER or BIGINT if it is not already typed as INTEGER, BIGINT, or CHARACTER, a value between 1 and the number of partitions defined for that level is deleted.

Otherwise, the system returns an error to the requestor.

WITH INSERT INTO save_table Null Partition Handling Clause

If you specify a WITH INSERT INTO save_table null partition handling clause, Vantage deletes any row for which a new partitioning expression does not result in a value between 1 and the number of partitions defined for that level and inserts it into save_table.

The following rules apply to this clause.

• save_table cannot be the same table as table_name or the system returns an error to the requestor.
• save_table must have the same number of columns with compatible data types as table_name, or the system returns an error to the requestor.

Rollback in Case of Errors

If evaluating the new partitioning expression for a row in the table causes evaluation errors to occur, Vantage rolls back any changes made to the table and to save_table and does not change the partitioning.

If errors occur when inserting into save_table, the following rollback behavior occurs, depending on the session mode.

New Partitioning

A new partitioning for a level does not become the partitioning for that level until it is successfully altered.

If you later attempt to insert or update a row of a row-partitioned table such that a partitioning expression for that row does not result in a value between 1 and the number of partitions defined for that level, the system returns an error to the requestor.

Changing Row Partitioning on a Populated Table

There is a negative performance impact if the table or join index has both column partitioning and row partitioning if the row partitioning is changed on a populated table because it is often costly to move a table row from one row partition to another.

Disable Triggers Before Modifying or Revalidating Table

You must disable the triggers specified in the following table before you can perform an ALTER TABLE request that modifies or revalidates the table.