Comparing ALTER TABLE TO CURRENT and ALTER TABLE … REVALIDATE - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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, Teradata Database 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, Teradata Database 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, Teradata Database 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, Teradata Database 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, Teradata Database 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.