Unique Primary Index (UPI) |
- is the most efficient access method when the SQL statement contains the PI value
- involves one AMP and one row
- requires no spool file (for a simple SELECT)
- can obtain the most granular locks
|
none, in the context of a SELECT statement specifying a PI value. However, a poorly chosen PI can cause poor overall performance in a large workload. |
Nonunique Primary Index (NUPI) |
- provides efficient access when the SQL statement contains the PI value
- involves one AMP
- can obtain granular locks
- may not require a spool file as long as the number of rows returned is small
|
- may slow down INSERTs for a SET table with no USIs.
- may decrease the efficiency of SELECTs containing the PI value when some values are repeated in many rows.
|
Unique Secondary Index (USI) |
- provides efficient access when the SQL statement contains the USI values, and you do not specify PI values
- involves two AMPs and one row
- requires no spool file (for a simple SELECT)
|
requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements. |
Nonunique Secondary Index (NUSI) |
- provides efficient access when the number of rows per value in the table is relatively small
- involves all AMPS and probably multiple rows
- provides access using information that may be more readily available than a UPI value, such as employee last name, compared to an employee number
- may require a spool file
|
- requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements.
- will not be used by the Optimizer if the number of data blocks accessed is a significant percentage of the data blocks in the table because the Optimizer will determine that a full table scan is cheaper.
|
Full table scan |
- accesses each row only once
- provides access using any arbitrary set of column conditions
|
- examines every row.
- usually requires a spool file possibly as large as the base table.
|
Multitable join index (JI) |
- can eliminate the need to perform certain joins and aggregates repetitively
- may be able to satisfy a query without referencing the base tables
- can have a different PI from that of the base table
- can replace an NUSI or a USI
|
- requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements for any of the base tables that contribute to the multitable JI.
- usually is not suitable for data in tables subjected to a large number of daily INSERT, UPDATE, MERGE, and DELETE statements.
- imposes some restrictions on operations performed on the base table.
|
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 a different PI from that of the base table
|
- requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements.
- imposes some restrictions on operations performed on the base table.
|
Sparse join index (JI) |
- can be stored in less space than an ordinary JI
- reduces the additional overhead associated with INSERT, UPDATE, MERGE, and DELETE statements to the base table when compared with an ordinary JI
- can exclude common values that occur in many rows to help ensure that the Optimizer chooses to use the JI to access less common values
|
- requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements to the base table.
- imposes some restrictions on operations performed on the base table.
|