17.10 - Comparing ALTER TABLE TO CURRENT and ALTER TABLE … REVALIDATE - 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 table compares ALTER TABLE TO CURRENT and ALTER TABLE … REVALIDATE. See General Rules and Restrictions for the REVALIDATE Option.

ALTER TABLE TO CURRENT ALTER TABLE ... REVALIDATE
Periodically modify the partitioning.
  • Updates partitioning information in the table header after an upgrade to a major release.
  • Corrects any improperly partitioned rows after restoring to a system architecture that is different from the one from which an archive was made.
  • Corrects improperly partitioned rows caused by a system problem.
Resolves the DATE, CURRENT_DATE, and CURRENT_TIMESTAMP to their current values. Uses the most recently resolved DATE, CURRENT_DATE, and CURRENT_TIMESTAMP values.
Updates the partitioning information in the table header. Updates the partitioning information in the table header.
When reconciling the rows of the specified table or join index, Vantage skips any partition when it can determine that all of the rows in the partition would remain in that partition after reconciliation.

This assumes that the rows of the table or join index are properly partitioned prior to submitting the ALTER TABLE TO CURRENT request.

When reconciling the rows of the specified table, Vantage scans all of its partitions if you specify a null partition handler.

This assumes that the rows of the table might not be properly partitioned before you submitted the current ALTER TABLE … REVALIDATE PRIMARY INDEX request.

You cannot specify this option for join indexes.

Reconciles all of the rows of the specified table or join index.
  • If the request specifies a null partition handler, Vantage deletes any rows that cannot be reconciled after saving them to a save table if the request specifies a WITH INSERT INTO save_table null partition handler.

    You cannot specify this option for join indexes because the rows from a join index cannot be deleted using an ALTER TABLE TO CURRENT request.

  • If the request does not specify a null partition handler, the system aborts the request if an existing row cannot be reconciled.
Only reconciles the rows of the specified table or join index if the request specifies a null partition handler.

If you specify a WITH INSERT null partition handler for a table, Vantage deletes any rows that cannot be reconciled after saving them in a save table if the request specifies a WITH INSERT INTO save_table null partition handler.

You cannot specify this option for join indexes.