Nonunique Primary Index Considerations
The following factors describe the general performance considerations associated with
At best, NUPIs distribute table rows evenly across the AMPs and hash values.
At worst, NUPI table row distribution can be skewed in various ways (see “SQL Scripts For Detecting Skew” on page 425) 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 files.
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
See “Duplicate Row Checks for NUPIs” on page 444 and “Using Unique Secondary Indexes to Enforce Row Uniqueness” on page 457 for further details.