15.00 - WITH INSERT [INTO] save_table and WITH DELETE Null Partition Handling Clauses - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

WITH INSERT [INTO] save_table and WITH DELETE Null Partition Handling Clauses

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 WITH INSERT [INTO] save_table or WITH DELETE null partition handling clause, a new partitioning expression for all rows currently in the table must, after casting to INTEGER or BIGINT if it is not already typed as INTEGER, BIGINT, or CHARACTER generate a value between 1 the number of partitions defined for that level.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

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

  • 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, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify a WITH INSERT [INTO] save_table null partition handling clause, Teradata Database 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 Teradata Database aborts the request and returns an error to the requestor.
  • save_table must have the same number of columns with compatible data types as table_name, or Teradata Database aborts the request and returns an error to the requestor.
  • If errors occur when inserting into save_table, the following rollback behavior occurs, depending on the session mode.
  •  

    FOR this session mode …

    Teradata Database rolls back the following block of work …

    ANSI

    request.

    Teradata

    transaction.

  • If evaluating the new partitioning expression for a row in the table causes evaluation errors to occur, Teradata Database rolls back any changes made to the table and to save_table and does not change the 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, Teradata Database aborts the request and returns an error to the requestor.

  • 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.
  • You must disable the triggers specified in the following table before you can perform an ALTER TABLE request that modifies or revalidates the table.
  •  

    IF you specify this clause in an ALTER TABLE request that modifies or revaluates the row partitioning …

    THEN you must disable these triggers before you perform the request …

    WITH DELETE

    all delete triggers on table_name.

    After the ALTER TABLE request completes, you can 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, you can re-enable the disabled triggers.