Hash Indexes - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Hash Indexes

Hash indexes are file structures that share properties in common with both single-table join indexes and secondary indexes (see “Comparison of Hash Indexes and Single-Table Join Indexes” on page 554).

From an architectural perspective, the incorporation of auxiliary structures as a transparently embedded element of the hash index column set is what most distinctly distinguishes hash indexes from single-table join indexes. These auxiliary structures are components of the base table, and are added to the hash index definition by default if they are not explicitly declared by the CREATE HASH INDEX column set definition. Because it is not clear what the default auxiliary structures Teradata Database uses when it create a hash index, you should always consider creating an equivalent single‑table join index in preference to a hash index. Also, multivalue compression from the base table may be carried over to a join index, but it is not carried over to a hash index.

If the columns you specify for the hash index column set duplicate the default auxiliary structure columns, then those columns are not added redundantly. The auxiliary structures provide indexed access to base table rows.

If you do not specify a partition key explicitly with the BY clause of the CREATE HASH INDEX statement, then the system adds this auxiliary pointer data to the hash index rows automatically and then uses it to partition them.