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