Use an HG index when:
The column will be used in a join predicate
A column has more than 1000 unique values
Use multicolumn HG indexes to enhance the performance of ORDER BY queries with reference to multiple columns. This change is transparent to users, but improves query performance. For an example, see “Enhancing ORDER BY query performance,” in Chapter 3, “Optimizing Queries and Deletions,” in the Performance and Tuning Guide.
Foreign key columns require their own, individual HG index.
However, if a join index exists, the same column cannot have both
an explicitly created HG index and a foreign key constraint.
