17.10 - Hash and Single-Table Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Fundamentals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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:
    • 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, Vantage 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 and FastLoad 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.