15.00 - Comparison of Hash and Single-Table Join Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Comparison of Hash and Single-Table Join Indexes

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 can also 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.

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, or update operation.
  • Both can be the object of any of the following SQL statements:
  •  
  • COLLECT STATISTICS (Optimizer Form)
  • DROP STATISTICS
  • HELP INDEX
  • SHOW HASH INDEX
  • Both receive their space allocation from the permanent space of their creator and are stored in distinct tables.
  • Both can be hash‑ or value‑ordered.
  • You must drop and rebuild all value‑ordered (but not hash‑ordered) hash and join indexes after you run the Reconfig utility. See Support Utilities.

  • Both can be row compressed, though the method of compression is different for each, and both are different from the method of multi-value compression used for base tables.
  • See “Compression of Hash Index Rows” on page 326, “Row Compression of Join Indexes” on page 380, and “Compressing Column Values Using Only Multi-Value Compression” on page 548, respectively, for the mechanisms used to compress hash index rows, join index rows, and base table column values.

    Note that while join index columns can inherit the multi-value compression characteristics of their parent tables, hash index columns cannot (see “Compressing Hash Index Column Values” on page 325).

  • Both can be FALLBACK protected.
  • Both can be used to transform a complex expression into a simple index column. The transformation permits you to collect statistics on the expressions, which can then be used by the Optimizer to make single‑table cardinality estimates when those expressions are specified on base table columns in the predicate of a query (see SQL Request and Transaction Processing for details).
  • A join index can specify expressions in its select list, while a hash index has no select list.
  • Neither can be queried or directly updated.
  • A hash index cannot have a partitioned primary index, but a single‑table join index can.
  • Neither can be used to partially cover a query that specifies the TOP n or TOP m PERCENT option.
  • Both share the same restrictions for use with the MultiLoad, Teradata Parallel Transporter, FastLoad, and Archive/Recovery utilities.
  • The following table summarizes the differences between hash and join indexes:

     

    Hash Index

    Join Index

    Indexes one table only.

    Can index multiple tables.

    A logical row corresponds to one and only one row in its referenced base table.

    A logical row can correspond to either of the following, depending on how the join index is defined:

  • One and only one row in the referenced base table.
  • Multiple rows in the referenced base tables.
  • Column list cannot specify aggregate or ordered analytical functions.

    Select list can specify aggregate functions.

    Cannot specify an expression in its select list.

    Can specify an expression in its select list.

    Cannot have a secondary index.

    Can have a secondary index.

    Supports transparently added, system‑defined columns that point to the underlying base table rows.

    Does not add underlying base table row pointers implicitly.

    Pointers to underlying base table rows can be created explicitly by defining one element of the column list using the keyword ROWID.

    Note that you can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement.

    Cannot be specified for a NoPI table.

    Can be specified for a NoPI table.

    Primary index cannot be partitioned.

    Primary index of uncompressed row forms can be partitioned.

    Cannot be defined on a table that also has triggers.

    Can be defined on a table that also has triggers.

    Cannot be defined on a table that also has triggers.

    Can be defined on a table that also has triggers.

    Column multi-value compression, if defined on a referenced base table, is not added transparently by the system and cannot be specified explicitly in the hash index definition.

    Column multi-value compression, if defined on a referenced base table, is added transparently by the system with no user input, but cannot be specified explicitly in the join index definition.

    Index row compression is added transparently by the system with no user input.

    Index row compression, if used, must be specified explicitly in the CREATE JOIN INDEX request by the user.

    Hash indexes provide a functional subset of the capabilities of join indexes. In other words, any hash index has a functionally equivalent join index. The only difference between a hash index and its functionally equivalent join index is that the hash index might be marginally smaller than its equivalent join index.

    To further emphasize this, each of the examples of creating a hash index at the end of the CREATE HASH INDEX topic is accompanied by an example that creates an equivalent join index. Note that the functionally equivalent join indexes include only the uniqueness part of the ROWID.