Compare the previous example of USI access to NUSI access. With NUSIs, each AMP contains an index structure for the base table rows that the AMP owns. The Optimizer cannot know if only a single row contains the specified NUSI value, so the NUSI subtables for all AMPs are searched, making NUSI accesses all-AMPs operations. The exceptions are when a NUSI is on the same columns as the primary index (as when the primary index is row-partitioned) or the NUSI is on the same columns as the primary AMP index. These cases require a single-AMP search.
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 cvc1708630630656.html#lgn1472240603228__BABJCCHH_xreftarget):
EXPLAIN SELECT s_suppkey, s_acctbal FROM supplier WHERE s_name = 'SUPPLIER#000000647';
Explanation ------------------------------------------------------------------ 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, and therefore require all-AMPs operations that are similar to NUSI access.