Few-AMP Joins and Tactical Queries
Even when joins must be made to process a tactical query, it is possible to make the join with few‑AMP operations. The following picture illustrates the combinations of accessing and joining that can be performed engaging one or few AMPs. In all of these few-AMP examples, the first table is accessed using either a UPI or a 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.
One idiosyncrasy of EXPLAIN text is that it sometimes says "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.