Single-AMP Operations Using a Primary Index - Teradata VantageCloud Lake

Single-AMP operations use a primary index value to locate a row.

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.