Cost of Join Index Maintenance - Teradata VantageCloud Lake

The largest resource burden for any join index is incurred by its maintenance: inserting, updating, and deleting rows in the join index table. Similar to creation costs, types of join indexes incur different maintenance costs.

Join indexes can be expensive to maintain. The most important factor in the decision to use a join index is likely to its maintenance cost.

Each time a join-indexed base table column is updated, the corresponding join index table column must also be updated. Each time a new row is added to or an existing row is deleted from a join-indexed base table, the corresponding join index table rows must also be inserted or deleted. Therefore, evaluate the benefit of a join index created to enhance your standard queries vis-a-vis its cost to create and maintain (see Cost/Benefit Analysis for Join Indexes).

Maintenance Cost Optimizations Based on Foreign Key-Primary Key Joins

Consider the added cost of join index maintenance carefully when you are designing the indexes for your data warehouse to make sure that the minimum number of join indexes can be called on by the Optimizer to cover the maximum number of queries. Designing with foreign key-primary key joins allows you to make these optimizations.

Whenever a base table column set that is shared with a join index is updated or deleted, or when a new row is inserted into the base table, the system generates extra steps to maintain the base table and join index concurrently. If the base table is specified as part of an outer join in the join index definition, the steps can be more complex because maintenance may be needed for both matched and unmatched row sets.

However, when the join columns have a foreign key-primary key relationship, the system treats inner and outer joins alike (see Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables than a Query).

Maintenance Cost Optimizations for DELETE ALL Operations

A fastpath optimization is one that can be performed faster if certain conditions are met. For example, in some circumstances DELETE and INSERT operations can be performed faster if they can avoid reading the data blocks and avoid transient journaling.