15.00 - Tradeoffs for Join or Hash Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Tradeoffs for Join or Hash Indexes

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” on page 614, “Archive and Recovery” on page 614, 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” on page 613.