17.10 - Tradeoffs for Join or Hash Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
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, Teradata® FastLoad Reference, B035-2411, and Teradata® MultiLoad Reference, B035-2409 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 (for details, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146).

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