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, B035-2412,
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. See “MERGE” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 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.