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

SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
zwv1557098532464.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1141
lifecycle
previous
Product Category
Teradata Vantageâ„¢

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.