Joins that must be made to process a tactical query can be made with few-AMP operations. The following picture shows the combinations of accessing and joining that can be performed engaging one or few AMPs. In these examples, the first table is accessed using a UPI or USI.
The first join in the following example is made using a merge join. Because the primary index columns of both tables share the same domain, their associated rows reside on the same AMP, with the second table being joined based on a NUPI. The remaining joins use combinations of primary index and USI access and perform few-AMP nested joins between the tables.
EXPLAIN text may say "we do a two-AMP join step" when a USI is defined on one half of the nested join. Three AMPs are engaged for most such joins, not two.