15.00 - Single-AMP Operations - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Single-AMP Operations

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

Single-AMP operations can be achieved by any of the following data manipulation operations:

  • 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:

         SELECT s_name, s_acctbal
         FROM supplier
         WHERE s_suppkey = 583;
      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.

    Notice that there are no references to locking in the EXPLAIN report for this query. That is because the Optimizer has folded the locking activity (in this case a single row hash READ lock) into the same step that retrieves the row. This sort of 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.