The reasons for using hash indexes are similar to those for using single-table join indexes. Hash indexes optionally be specified to be distributed in such a way that their rows are AMP-local with their associated base table rows and provide an alternate direct access path to those base table rows. In this way, hash indexes similar in function to secondary indexes. Hash indexes are also useful for covering queries without having to access the base table.
- 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 the following SQL statements: COLLECT STATISTICS (Optimizer Form), DROP STATISTICS, HELP INDEX, or 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-ordered or value-ordered.
- Both can be row-compressed, though the method of compression is different for each, and both are different from the method of multivalue compression used for base tables.
For a description of the methods used to compress hash index rows, join index rows, and base table column values, see Compression of Hash Index Rows, Row Compression of Join Indexes, and Compressing Column Values Using Only Multivalue Compression.
Although join index columns can inherit the multivalue compression characteristics of their parent tables, hash index columns cannot. See Compressing Hash Index Column Values.
- 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 Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
- 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 or primary AMP 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:
|
| 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. 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 noncompressed 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 multivalue 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 multivalue 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. A hash index has a functionally equivalent join index. The functionally equivalent join indexes include only the uniqueness part of the ROWID. See CREATE HASH INDEX examples in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.