Hash indexes are similar to single-table simple join indexes in that they are used for denormalizing a single table by storing rows in a separate physical object from the base table. Hash indexes are limited to a SINGLE table and are beneficial in improving efficiency.
Like join indexes, Hash index rows are stored in a different object than the base table rows. This allows for faster access without needing access to the underlying rows. Also like their relative, they function as an alternative to the base rows instead of providing a different access path.
Like join indexes, hash indexes are known as covered queries because they are utilized when they contain all the columns requested by the query. If all of the columns are covered, the optimizer will usually choose to access the rows via the hash index instead of the base table rows. In a situation where the hash index partially covers the query, the optimizer may use the Row ID included with the Hash Index to access the other columns in the data row. Join indexes and hash indexes are both transparently maintained by Teradata. When the base table changes, the Hash Index table is automatically updated. This automatic task is referred to as an update burden. Being that Hash Indexes are strikingly similar in functionality to secondary indexes, they should be carefully considered because they carry this update burden.
Hash indexes however, can offer marginal performance gains over a secondary index when certain columns are frequently needed from one table to join with many others. In these situations, the Hash index partially covers the query, but fully covers the rows from the base table, effectively eliminating any need to access the base table. Hash Indexes: Can be ordered by hash or by values to facilitate range queries. Automatically has the Row ID of the base table row included in the Hash Index which the RDBMS software can use to access columns not included in a covered query