The following factors describe the general performance considerations associated with NUPIs.
- At best, NUPIs distribute table rows evenly across the AMPs and hash values.
At worst, NUPI table row distribution can be skewed (see SQL Scripts for Detecting Skew) and, if so, decrement both retrieval and update performance.
- NUPIs at best can effect good retrieval and update performance.
- NUPI values can involve more than one base table row.
- NUPI retrievals can require spool.
- Duplicate NUPI values hash to the same AMP and often are stored in the same data block.
- If all the rows for a duplicate NUPI hash value fit into a single data block, then only 1 or 2 I/Os are required to store or access the entire set.
2 I/Os are only required when the cylinder index is not cached.
- If duplicate rows are excluded because the table is defined as SET and has no uniqueness constraint, then the system must make a duplicate row check for every table row inserted or updated.
See Duplicate Row Checks for SET Tables with NUPIs and Using Unique Secondary Indexes to Enforce Row Uniqueness for further details.