The reasons for using hash indexes are similar to those for using single-table join indexes. Not only can hash indexes optionally be specified to be distributed in such a way that their rows are AMP-local with their associated base table rows, they also implicitly provide an alternate direct access path to those base table rows. This facility makes hash indexes somewhat similar to secondary indexes in function. Hash indexes are also useful for covering queries so that the base table need not be accessed at all.
The following list summarizes the similarities of hash and single-table join indexes:
- Primary function of both is to improve query performance.
- Both are maintained automatically by the system when the relevant columns of their base table are updated by a DELETE, INSERT, UPDATE, or MERGE statement.
- Both can be the object of any of the following SQL statements:
- COLLECT STATISTICS
- DROP STATISTICS
- HELP INDEX
- SHOW
- Both receive their space allocation from permanent space and are stored in distinct tables.
- The storage organization for both supports a compressed format to reduce storage space, but for a hash index, Teradata Database makes this decision.
- Both can be FALLBACK protected.
- Neither can be queried or directly updated.
- Neither can store an arbitrary query result.
- Both share the same restrictions for use with the MultiLoad, FastLoad, and Archive/Recovery utilities.
- A hash index implicitly defines a direct access path to base table rows. A join index may be explicitly specified to define a direct access path to base table rows.