Examples of Row-at-a-Time INSERT Maintenance Overhead - Teradata Database

Examples of Row-at-a-Time INSERT Maintenance Overhead

Using the guidelines provided in “Join and Hash Index Maintenance Considerations” on page 516, you can create a single-table join index that performs well with continuous row‑at‑a-time inserts to its base table. The maintenance to that join index structure, if Teradata Parallel Data Pump were performing continuous inserts, would be one additional single-AMP operation accompanied by a single rowhash-level WRITE lock.

A further example of join index maintenance shows that row-at-a-time inserts into a base table that participates in a multitable join index exert a table‑level lock or a partition-level lock (if partition elimination is possible) on the join index except for the following cases:

The primary index of the join index is composed of a column set from the target table of the simple insert.

The primary index of the join index is composed of a column set that is joined either directly or indirectly through transitive closure to the target table of the simple insert.

In the following example, the primary index of a row-compressed multitable join index is a subset of the base table being updated, so a single‑AMP merge can be used.

If neither exception is true, then consider using set processing approaches to update the base table (see “Set Processing Alternative” on page 522).

An aggregate join index incurs the same maintenance overhead as a multitable join index for row-at-a-time base table inserts. The following example uses a single‑table aggregate join index.

For multitable join indexes, the Optimizer uses rowhash‑level locks wherever possible, resulting in fewer table‑level locks on the join index table. If there are just a few rows in the join index that are impacted, the Optimizer places several rowhash‑level locks on just those AMPs and then uses a group‑AMPs, rather than an all-AMPs operation.