15.00 - Advantages of Indexing Over Hashing - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Advantages of Indexing Over Hashing

There are two categories of query that often perform better with value-ordered indexing.

  • Range queries
  • Retrievals having selection criteria that involve only part of a multicolumn hash key
  • First consider range queries. The following SQL SELECT statement requests all rows from the parts table with a part number ranging between 3517 and 3713, inclusive. The primary key for the table is part_number.

         SELECT part_number, part_description
         FROM parts_table
         WHERE part_number BETWEEN 3517 AND 3713;

    Depending on the configuration and the database manager, such a query might perform better if executed against a table that uses a value-ordered primary index key rather than a hash-ordered hash key.

    To enhance the ability of hash keys to retrieve range query data, Teradata Database provides two mechanisms: the capability to store index rows in the order of their index values and the capability to store base table rows within partitions, including range partitions, after they have been hashed to an AMP. See “CREATE INDEX,” “CREATE JOIN INDEX,” and “CREATE TABLE” in SQL Data Definition Language and “CASE_N” and “RANGE_N” in SQL Functions, Operators, Expressions, and Predicates.

    The only way to avoid the partial hash key issue is to have a thorough understanding of your applications and data demographics before you define your indexes. In other words, if your applications will use partial key selection criteria, either define the index on those frequently retrieved columns or define a secondary index on them. The optimal solution is very dependent on the individual circumstances and there is no single correct way to design for this particular situation.