Hash indexes are used for the same purposes as are single-table join indexes, and are less complicated to define. However, a join index offers more choices.
| Hash Index | Single-Table Join Index |
|---|---|
| Column list cannot contain aggregate or ordered analytical functions. | Column list can contain aggregate functions. |
| Cannot have a unique primary index. | A non-compressed and nonvalue-ordered single-table join index can have a unique primary index. |
| Cannot have a secondary index. | Can have a secondary index. |
| Supports transparently added, system-defined columns that point to the underlying base table rows. | Does not implicitly add underlying base table row pointers. Pointers to underlying base table rows can be created explicitly by defining one element of the column list using the ROWID keyword or the UPI or USI of the base table. |
| Cannot be defined on a NoPI table. | Can be defined on a NoPI table. |
Hash indexes are useful for creating a full or partial replication of a base table with a primary index on a foreign key column to facilitate joins of very large tables by hashing them to the same AMP.
You can define a hash index on one table only. The functionality of hash indexes is a subset to that of single-table join indexes.