| Unique Primary Index (UPI) |
- Most efficient access when SQL statement specifies PI value.
- Involves one AMP and one row.
- Requires no spool file (for a simple SELECT).
- Can obtain the most granular locks.
|
None if SELECT statement specifying a PI value. However poorly chosen PI can cause poor overall performance in large workload. |
| Nonunique Primary Index (NUPI) |
- Efficient access when SQL statement specifies PI value.
- Involves one AMP.
- May not require spool file if number of rows returned is small.
- Can obtain granular locks.
|
- May slow down INSERTs for SET table with no USIs.
- May decrease efficiency of SELECTs specifying PI value when some values are repeated in many rows.
|
| Unique Secondary Index (USI) |
- Efficient access when SQL statement specifies USI values but not PI values.
- Involves two AMPs and one row.
- Requires no spool file (for simple SELECT).
|
Additional overhead for INSERT, UPDATE, MERGE, and DELETE statements. |
| Nonunique Secondary Index (NUSI) |
- Efficient access when number of rows per value in table is relatively small.
- Involves all AMPS and probably multiple rows.
- Uses information that may be more readily available than UPI value, such as employee last name, compared to employee number.
- May require spool file
|
- Additional overhead for INSERT, UPDATE, MERGE, and DELETE statements.
- Not used by Optimizer if number of data blocks accessed is significant percentage of data blocks in table, because Optimizer determines that full table scan is cheaper.
|
| Full table scan |
- Accesses each row only once.
- Access uses arbitrary set of column conditions.
|
- Examines every row.
- Usually requires spool file, possibly as large as base table.
|
| Multitable join index (JI) |
- Can eliminate repetition of joins and aggregates.
- May satisfy query without referencing base tables.
- Can have different PI from that of base table.
- Can replace NUSI or USI.
|
- Additional overhead for INSERT, UPDATE, MERGE, and DELETE statements for base tables that contribute to it.
- Usually unsuitable for data in tables subjected to many daily INSERT, UPDATE, MERGE, and DELETE statements.
- Restricts operations on base tables.
|
| Single-table join index (JI) or
hash index
|
- Can isolate frequently used columns (or their aggregates for JIs only) from those that are seldom used.
- Can reduce number of physical I/Os when only commonly used columns are referenced.
- Can have different PI from that of base table
|
- Additional overhead for INSERT, UPDATE, MERGE, and DELETE statements.
- Restricts operations on base table.
|
| Sparse join index (JI) |
- Needs less space than ordinary JI.
- Has less overhead for INSERT, UPDATE, MERGE, and DELETE statements to base table than ordinary JI.
- Can exclude values common to many rows to increase chance that Optimizer chooses to use JI to access less common values.
|
- Additional overhead for INSERT, UPDATE, MERGE, and DELETE statements to base table or tables.
- Restricts operations on base table or tables.
|