15.00 - Two-AMP Operations: USI Access and Tactical Queries - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Two-AMP Operations: USI Access and Tactical Queries

When an application specifies a value that can be used to access a table using its USI, a 2‑AMP operation results. Note that USI access can be a single‑AMP operation if the USI value for a row happens to hash to a subtable on the same AMP as the primary index for the same row, but is never more than a 2‑AMP operation.

In the following query, the column s_name is defined as a USI on the original supplier table having s_suppkey as its UPI:

     EXPLAIN 
     SELECT s_suppkey, s_acctbal 
     FROM supplier
     WHERE s_name = 'Supplier#000038729';
 
Explanation
------------------------------------------------------------------------
  1) First, we do a two-AMP RETRIEVE step from CAB.supplier by way
     of unique index # 8 "CAB.supplier.S_NAME = 'Supplier#000038729'" with no 
     residual conditions.  The estimated time for this step is 0.07 seconds.