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

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
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, 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.