15.00 - Maintenance Costs for Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Maintenance Costs for Join Indexes

The use of a join index entails the following.

  • Initial time consumed to calculate and create the index
  • Whenever a value in a join index column of the base table is updated, the join index must also be updated, including any required aggregation or pre-join effort.
  • However, if join indexes are suited to your applications, the improvements in request performance can far outweigh the costs.

    Join indexes are maintained by generating additional AMP steps in the base table update execution plan. Those join indexes defined with outer joins usually require additional steps to maintain any unmatched rows.

    Expect a single-table join index INSERT operation to have similar maintenance overhead as would an insert operation with an equivalent NUSI. UPDATE or DELETE operations, however, might incur greater overhead with a single-table join index, unless a value for the primary index of the join index is available at the time of the update.

    Overhead for an in-place aggregate join index can be perhaps 3 times more expensive than maintaining the same table without that index. For an aggregate join index that redistributes rows, the maintenance overhead can be several times as expensive.

    Maintenance overhead for multitable join indexes without aggregates can be small or very large, depending on the pre-join effort involved in constructing or changing a join index row. This could be up to 20 times or more expensive than maintaining the table without the index. The overhead is greater at higher hits per block, where hits means the number of rows in a block are touched.

    Since Teradata Database writes a block only once regardless of the number of rows modified, as the number of hits per block increases:

  • The CPU path per transaction decreases (faster for the case with no join index than for the case with a join index)
  • Maintenance overhead for aggregate join indexes decreases significantly
  • If a DELETE or UPDATE request specifies a search condition on the primary index or secondary index of a join index, the join index may be directly searched for the qualifying rows and modified accordingly.

    This direct-update approach is employed when the request adheres to these requirements:

  • A primary index or secondary index access path to the join index
  • If a join_index_column_2 is defined, little or no modification to the join_index_column_1 columns
  • No modifications to the join condition columns in the join index definition
  • No modifications to the primary index columns of the join index
  • It is not necessary to drop the join index before a backup. It is important, however, to drop join indexes before the underlying tables and databases are restored, should a restore ever be required. Otherwise an error is reported and the restore will not be done.