All AMPs are engaged for product and hash joins, and also for most merge joins. For this reason these join methods are less desirable for more localized queries. Tactical queries benefit from the few-AMP joins described in All-AMP Queries.
The most beneficial for tactical queries is the nested join, which can involve only a few AMPs.
A few-AMP nested join is possible when the first table can be accessed by a UPI or USI specified by the request. A foreign key within that first table must be available to drive a nested join into a second table, which contains its associated primary key. For this nested join to involve one or few AMPs, the second table must have a primary index or a USI defined on the column set mapped to by the foreign key in the first table, as demonstrated by the following graphic:
