15.00 - Hash Index Applications - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Hash Index Applications

Hash indexes are useful for queries where the index table contains the columns referenced by a query, thereby allowing the Optimizer to cover it by planning to access the index rather than its underlying base table. An index that supplies all the table columns requested by a query is said to cover that table for that query and, for obvious reasons, is referred to as a covering index. Note that query covering is not restricted to hash indexes: other indexes can also cover queries.

Hash indexes can be defined on a table in place of secondary indexes. This usage makes more sense when you distribute the hash index row to the AMPs so that it facilitates a wider range of query processing than a secondary index might. Because hash indexes can potentially carry more of an update burden than secondary indexes, you should not define them on a table when a secondary index would serve the same intended function. Keep in mind that this depends on how the updates are done. For example, a single row update might be faster with a secondary index, but an INSERT … SELECT might be faster with a hash index.