16.10 - Tradeoffs for Join or Hash Indexes - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Join and hash indexes can also have properties that make other solutions more optimal. Remember: it is always important to test and validate join and hash indexes on a test system before putting them into a production environment. Keep these potential downside characteristics in mind whenever you consider creating a join or hash index that you think might be useful for your application workload.

  • Balance usage broadness with access efficiency. The more columns defined for a join or hash index, the longer the access time for processing the index. The more tables included in a hash or join index definition, the longer and more burdensome the required maintenance time.
  • You cannot define row partitioning for a hash index, nor can you define row or column partitioning for a row-compressed join index.

    However, you can define row or column partitioning for an uncompressed join index.

    Note that you cannot collect statistics on the PARTITION column of such a join index.

  • You cannot use the MultiLoad, FastLoad, or Restore utilities against tables that have join or hash indexes defined on them. See Load Utilities, Archive and Recovery, Teradata Archive/Recovery Utility Reference, Teradata FastLoad Reference, and Teradata MultiLoad Reference for further information.

    For many bulk loading applications, you can instead FastLoad rows into a staging table which you then MERGE (using error logging) into the target table. See “MERGE” in SQL Data Manipulation Language for details.

  • You cannot define triggers on tables that have hash indexes defined on them; however, you can define join indexes and triggers on the same table. See Triggers.