15.00 - Using ALTER TABLE TO CURRENT Requests to Reconcile Join Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Using ALTER TABLE TO CURRENT Requests to Reconcile Join Indexes

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 Teradata Database 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 delete 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. Keep in mind that if you decide to use this method, Teradata Database 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 both 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.