Examples of Row-at-a-Time INSERT Maintenance Overhead
Using the guidelines provided in “Join and Hash Index Maintenance Considerations” on page 566, 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 on the join index except for the following cases:
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 572).
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.