15.00 - Hash Indexes - Teradata Database

Hash Indexes

Hash indexes are used for the same purposes as single-table join indexes. The following table lists the principal differences between hash indexes and single-table join indexes.

Hash Index	Single-Table Join Index
Column list cannot contain aggregate or ordered analytical functions.	Column list can contain aggregate functions.
Cannot have a unique primary index.	A non-compressed and nonvalue-ordered single-table join index can have a unique primary index.
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 implicitly add underlying base table row pointers. Pointers to underlying base table rows can be created explicitly by defining one element of the column list using the ROWID keyword or the UPI or USI of the base table.
Cannot be defined on a NoPI table.	Can be defined on a NoPI table.

Hash indexes are useful for creating a full or partial replication of a base table with a primary index on a foreign key column to facilitate joins of very large tables by hashing them to the same AMP.

You can define a hash index on one table only. The functionality of hash indexes is a subset to that of single-table join indexes.

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

Effects of Hash Indexes

Hash indexes affect Teradata Database functions and features the same way join indexes affect Teradata Database functions and features. For details, see “Effects of Join Indexes” on page 43.

Queries Using a Hash Index

In most respects, a hash index is similar to a base table. For example, you can perform COLLECT STATISTICS, DROP STATISTICS, HELP, and SHOW statements on a hash index.

Unlike base tables, you cannot do the following things with hash indexes:

Query or update hash index rows explicitly.

Store and maintain arbitrary query results such as expressions.

Create explicit unique indexes on its columns.

Partition the primary index of the hash index.

Related Topics

For information on …	See …
using CREATE HASH INDEX to create a hash index	SQL Data Definition Language.
using DROP HASH INDEX to drop a hash index
using HELP HASH INDEX to display the data types of the columns defined by a hash index
database design considerations for hash indexes	Database Design.

Consult the following books for more detailed information on using hash indexes to enhance the performance of your databases:

Database Design

SQL Data Definition Language