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

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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.

Session Mode Block of Work Rolled Back
ANSI Request.
Teradata Transaction.

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.
ALTER TABLE Clause to Modify or Revalidate Row Partitioning Triggers to Disable
WITH DELETE All delete triggers on table_name.

After the ALTER TABLE request completes, re-enable the disabled triggers.

WITH INSERT INTO save_table
  • All delete triggers on table_name.
  • All insert triggers on save_table.

After the ALTER TABLE request completes, re-enable the disabled triggers.