15.00 - ALTER TABLE TO CURRENT - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

ALTER TABLE TO CURRENT

Comparing the Capabilities of ALTER TABLE TO CURRENT Requests With ALTER TABLE … REVALIDATE PRIMARY INDEX Requests

The following table compares the relative functionality of ALTER TABLE TO CURRENT requests and ALTER TABLE … REVALIDATE PRIMARY INDEX (see “General Rules and Restrictions for the REVALIDATE Option” on page 129) requests.

 

                                       ALTER TABLE TO CURRENT

 ALTER TABLE … REVALIDATE PRIMARY INDEX

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.