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

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Comparison of Hash Indexes 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 a transparent direct access path to those base table rows to complete a query only partially covered by the index. 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 most apparent external difference between hash and single-table join indexes is in the syntax of the SQL statements used to create them. The syntax for CREATE HASH INDEX is similar to that for CREATE INDEX. As a result, it is simpler to create a hash index than to create a functionally comparable single-table join index.

The following list summarizes the similarities of hash and single-table join indexes.

  • The 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 statement.
  • 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 permanent space 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.
  • Both can be FALLBACK protected.
  • Both can be used to transform a complex expression into a simple index column. This transformation permits you to collect statistics on the expression, which the Optimizer can then use to make single‑table cardinality estimates for a matching complex column predicate specified on the base table and for mapping a query expression that is identical to an expression defined in the join index, but is found within a non‑matching predicate (see SQL Request and Transaction Processing for details).
  • Neither can be queried or directly updated.
  • A hash index cannot have a partitioned primary index, but a single‑table join index can.
  • A hash index must have a primary index, but a single‑table join index can be created with or without a primary index if it is column-partitioned.
  • A hash index cannot be column‑partitioned, but a single‑table join index can be column‑partitioned.
  • Neither can be used to partially cover a query that contains a TOP n or TOP m PERCENT clause.
  • Neither can be implemented with row compression if they specify a UDT in their select list because both create an internal column1 and column2 index when compressed.
  • Neither can be defined using the system‑derived PARTITION column.
  • Both share the same restrictions for use with the MultiLoad, FastLoad, and Archive/Recovery utilities.
  • The following table summarizes the important differences between hash and join indexes.

     

    Hash Index

    Join Index

    Indexes one table only.

    Can index multiple tables.

    This is not true for column‑partitioned join indexes, which can only index a single table.

    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.

    Can specify a UDT column in its column list.

    Can only specify a UDT in its select list if it is not row‑compressed.

    Cannot have a non-unique secondary index.

    Can have a non-unique secondary index.

    Supports transparently added, system‑defined primary index 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. See “CREATE JOIN INDEX” in SQL Data Definition Language Detailed Topics.

    Cannot be specified for NoPI or column‑partitioned base tables.

    Can be specified for both NoPI and column‑partitioned base tables.

    Cannot be column partitioned.

    Can be column partitioned.

    Cannot be row partitioned.

    Primary index of uncompressed row forms and column‑partitioned join indexes, can be row partitioned.

    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.

    It is possible to define a join index that has nearly the identical functionality to a hash index. The only essential differences between hash and join indexes is their respective DDL creation syntax. Otherwise, the functionality of hash indexes is a proper subset of the functionality of join indexes.