Single-AMP operations use a primary index value to locate a row.
- Simple single-row inserts
- Simple selects, updates and deletes qualified with a primary index value
- Joins between two tables that share one of the following characteristics:
- The same primary index domain
- A primary index making up the join constraint
- A single primary index value is specified for the join
The following EXPLAIN report is for a query that accesses the supplier table using the single primary index value s_suppkey = 583. Only a single AMP is engaged, as demonstrated by the single-AMP RETRIEVE step reported in step 1 of the EXPLAIN text:
EXPLAIN SELECT s_name, s_acctbal FROM supplier WHERE s_suppkey = 583;
Explanation
------------------------------------------------------------------------
1) First, we do a single-AMP RETRIEVE step from TPCD50G.supplier by
way of the unique primary index "TPCD50G.supplier.S_SUPPKEY = 583"
with no residual conditions. The estimated time for this step is
0.03 seconds.
There are no references to locking in the EXPLAIN report for this query, because the Optimizer has folded the locking activity (a single row hash READ lock) into the step that retrieves the row. This lock folding is done only with row hash locks.
This special shortcut for handling row hash locks eliminates the need for the Dispatcher to dispatch a separate locking step when only one AMP and one row are involved. This reduces the PE-to-AMP communication effort.