The following factors apply equally to UPIs and NUPIs.
- Each table has 0 or 1, and no more than 1, primary index.
- The primary index for a table need not be isomorphic with the primary key for the table. This is obviously true for NUPIs, because primary key values are unique by definition.
- A primary index can contain nulls, while a primary key cannot.
Primary index nulls are not good. If you think the primary index for a table may frequently be null, consider using a different column.
- All single-value primary index accesses are confined to a single AMP. In most cases, a single-value primary index access requires only 1 I/O.
If the cylinder index is not in cache, a primary index access requires 2 I/Os.