Using the guidelines provided in Join Index Maintenance Considerations, 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 is performing continuous inserts, is one additional single-AMP operation accompanied by a single rowhash-level WRITE lock.
- 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).
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 multiple-table join indexes, the Optimizer uses rowhash-level locks wherever possible, resulting in fewer table-level locks on the join index table. If few rows in the join index that are impacted, the Optimizer places rowhash-level locks on only those AMPs and then uses a group-AMPs, rather than an all-AMPs operation.