15.00 - NUSI Access and Performance - Teradata Database

Teradata Database Design

Teradata Database
User Guide

NUSI Access and Performance

NUSI access specifies a three-part BYNET message that is identical to the three-part message used for primary index access except that the subtable ID in the message references the NUSI subtable rather than the base table.

NUSI requests are all-AMP requests unless the NUSI is defined on the same columns as primary index.

The usefulness of a NUSI is correlated with the number of rows per value: the higher number of rows per value, the less useful the index. If the number of rows for a NUSI value exceeds the number of data blocks in the table, the usefulness of the NUSI might be questionable. On the other hand, as NUSI values approach uniqueness (meaning that the number of rows per value is either close to 1 or is significantly less than the number of AMPs in the system), an all‑AMPs table access is wasteful and you should consider defining a join index (see Chapter 11: “Join and Hash Indexes”) to support DML requests against the table instead of a NUSI.

Because NUSI access is usually an all‑AMPs operation, NUSIs may seem to have limited value. If you have to access all AMPs in the configuration to locate the requested rows, why bother with an index?

The answer to that question is provided by the following list of ways that NUSIs can improve the performance of your decision support queries:

  • NUSI access is often faster than a full‑table scan, particularly for extremely large tables. A full-table scan is also an all-AMP operation.
  • A NUSI that covers (see “NUSIs and Query Covering” on page 482 for a definition of covering) the columns requested by a query is often included in Optimizer access plans.
  • A NUSI that covers a LIKE expression or any selective inequality conditions is often included in Optimizer access plans.
  • A NUSI on the same columns as the primary index (this is only allowed when the primary index does not include all the columns of all the partitioning columns) may be more efficient than accessing using the primary index when there is no or limited partition elimination for a query.
  • While NUSI access is usually an all‑AMPs operation, keep in mind that the AMPs work in parallel. If all the AMPs have qualified rows, then this is a very efficient operation. If some or many of the AMPs do not have qualified rows, then those AMPs are doing work just to determine that they have no qualified rows. Note that if there are more rows per NUSI value than AMPs, it is likely that every AMP will have one or more qualified rows.

    At the same time, as the number of rows pointed to per NUSI value increases, the efficiency of a NUSI read decreases proportionately. Depending on demographics and environmental cost variables, the Optimizer will specify a full‑table scan instead of a NUSI access when it determines that the scan would be a more efficient access method.

    The flow diagram illustrates the following query.

         SELECT *
         FROM table_name
         WHERE NUSI_column = ‘CA’;

    The process used by this example for locating a row using the NUSI value CA is as follows.

    1 After checking the syntax and lexicon of the query, the Parser looks up the TableID for the NUSI subtable that contains the NUSI value CA.

    2 The hashing algorithm hashes the NUSI value.

    3 The Generator creates an AMP steps message containing the NUSI TableID (734596), NUSI row hash value (53), and NUSI data value (CA) and then the Dispatcher distributes it across the BYNET to all AMPs.

    4 The file system on a receiving AMP locates the appropriate NUSI subtable using its TableID.

    5 The file system on a receiving AMP uses the NUSI row hash value to locate the appropriate index row in the subtable.

    6 If there is a NUSI row, its table rowID list is scanned for base table row IDs.

    7 The file system uses the row IDs to locate the base table rows containing the NUSI value CA.

    For more information about secondary indexes and performance, see “Unique Secondary Indexes and Performance” on page 460.