15.00 - NUSI Access and Tactical Queries - Teradata Database

Teradata Database Design

Teradata Database
User Guide

NUSI Access and Tactical Queries

Compare the previous example of USI access to access using a NUSI. With NUSIs, each AMP contains an index structure for the base table rows that it owns. Even if only a single row contains the specified NUSI value, the Optimizer cannot know that a priori, so the NUSI subtables for all AMPs are always searched, making NUSI accesses all‑AMPs operations.

The following example assumes the s_name column is defined as a NUSI on supplier. Notice the difference between this EXPLAIN report and the previous one for the USI (see “Two-AMP Operations: USI Access and Tactical Queries” on page 898):

     SELECT s_suppkey, s_acctbal 
     FROM supplier
     WHERE s_name = 'SUPPLIER#000000647';
       1) First, we lock TPCD50G for read on a RowHash (proxy lock)
          to prevent global deadlock for TPCD50G.supplier.
       2) Next, we lock TPCD50G.supplier for read.
       3) We do an all-AMPs RETRIEVE step from TPCD50G.supplier by way of
          index # 8 "TPCD50G.supplier.S_NAME = 'SUPPLIER#000000647'" with no
          residual conditions into Spool 1, which is built locally on the
          AMPs.  The size of Spool 1 is estimated with high confidence to be
          1 row.  The estimated time for this step is 0.20 seconds.
       4) Finally, we send out an END TRANSACTION step to all AMPs involved
          in processing the request.

With NUSI access, table‑level locks are applied and an all-AMPs operation is performed. Value-ordered indexes and covering indexes are variants of a NUSI. Because of that, they also require all-AMPs operations that are similar to NUSI access.