Using ALTER TABLE TO CURRENT Requests to Reconcile Join Indexes - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The ALTER TABLE TO CURRENT statement enables refreshing join index rows without having to drop, and then recreate, that index. The efficiency of using this method to refresh join index rows compared to the drop-and-create alternative depends on two factors.

  • How frequently you must submit ALTER TABLE TO CURRENT requests to refresh join index rows.
  • The type of DATE, CURRENT_DATE, or CURRENT_TIMESTAMP condition that is specified in the join index definition.

If the join index rarely requires refreshing and its DATE, CURRENT_DATE, or CURRENT_TIMESTAMP condition is such that Vantage must remove a large volume of old rows and then insert a large number of new rows, it is usually more efficient to drop and then recreate the join index.

For obvious cases, such as when the primary index column of a join index is updated because of a new DATE, CURRENT_DATE, or CURRENT_TIMESTAMP value, ALTER TABLE TO CURRENT internally deletes all of the rows from the join index and then rebuilds it using the new DATE, CURRENT_DATE, or CURRENT_TIMESTAMP value.

For the less obvious cases for which the DELETE ALL option followed by an index rebuild operation is more efficient, but for some reason is not used by ALTER TABLE TO CURRENT, you should consider using the drop-and-recreate method. If you decide to use this method, Vantage drops the existing privileges and statistics on the join index when it drops the join index, so be sure to factor the time required to grant the dropped privileges and to recollect statistics into your determination of whether it would be the better approach or not.

When you must issue ALTER TABLE TO CURRENT requests for the base table and the join indexes defined on that table, you should consider submitting the ALTER TABLE TO CURRENT requests only on those join indexes that specify a lower bound DATE, CURRENT_DATE, or CURRENT_TIMESTAMP condition first. A lower bound DATE, CURRENT_DATE, or CURRENT_TIMESTAMP condition is one that causes rows from the join index to be deleted when the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP is refreshed to the latest date or timestamp value, for example using a condition such as j > CURRENT_DATE.

The outcome of this method is that any join index maintenance resulting from the ALTER TABLE TO CURRENT request on the base table does not also need be submitted unnecessarily on those join index rows that would have been deleted by the ALTER TABLE TO CURRENT request on the join index.