Hash Indexes
Hash indexes are used for the same purposes as single-table join indexes. Because single-table join indexes are more flexible in their definition options, and are essentially equivalent in function to hash indexes, you should use equivalent single‑table join indexes in preference to hash indexes.
The principal difference between hash and single-table join indexes are listed in the following table.
|
Hash Index |
Single-Table Join Index |
|
Cannot have secondary indexes. |
Can have secondary indexes. |
|
Rows are partitioned on the hash of the primary index of its base table unless otherwise explicitly specified. |
Rows are partitioned on an explicitly defined primary index. |
|
Column list cannot contain aggregate functions. |
Column list can contain aggregate functions. |
|
CREATE HASH INDEX syntax is similar to the syntax for CREATE INDEX. |
CREATE JOIN INDEX syntax specifies a subquery, making it superficially similar to the syntax for CREATE VIEW. |
|
Cannot be specified for a NoPI table, a primary-AMP-indexed table, a column‑partitioned table, or a global temporary trace table. |
Can be specified for a NoPI table, a primary-AMP-indexed table, or a column-partitioned table. |
Hash indexes create a full or partial replication of a base table with a primary index, usually on a foreign key column table 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 a hash index is the same as that of an equivalently defined single-table join index.
Hash indexes, like join indexes, are not indexes in the usual sense of the word. They are tables that you cannot access directly using DML requests.
The Optimizer includes a hash index in a query plan when the index covers all or part of a join query, thus eliminating the need to redistribute rows to make the join. In the case of partial query covers, the Optimizer uses certain implicitly defined elements in the hash index to join it with its underlying base table to pick up the base table columns necessary to complete the cover.