This chapter describes join and hash indexes. These indexes permit you to undertake a wide variety of physical denormalizations of the database without affecting the normalization of the logical and physical models.
You should always define an equivalent single‑table join index rather than a hash index so as not to depend on the default choices that are made for a hash index to be the correct choices. Also, hash indexes have some limitations such as multivalue compression is not carried over to a hash index from the base table.
Other topics include information on how to estimate the overhead of various join indexes, Optimizer criteria for selecting a join index, special storage options offered by join indexes, and numerous examples.
See Chapter 16: “Design Issues for Tactical Queries” for a description of the special design considerations that must be evaluated for using hash and join indexes to support tactical queries.
For information about design issues related to join indexes defined on temporal tables and system‑defined join indexes, see ANSI Temporal Table Support and Temporal Table Support.