NUSI requests are all-AMP requests unless the NUSI is defined on the same columns as a primary index or primary AMP 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 is questionable. However, 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. Consider defining a join index (see Join Indexes) to support DML requests against the table instead of a NUSI.
Because NUSI access is typically 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?
- 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 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 typically an all-AMPs operation, AMPs work in parallel. If all the AMPs have qualified rows, then this is an efficient operation. If there are AMPs without qualified rows, those AMPs do work to determine their absence of qualified rows. If there are more rows per NUSI value than AMPs, every AMP is likely to have one or more qualified rows.
Depending on demographics and environmental cost variables, the Optimizer specifies a full-table scan instead of a NUSI access when the scan is a more efficient access method.