Types of Join Index
There are four basic join index types defined over two orthogonal dimensions: single or multitable and simple or aggregate. These are better thought of as ways to group functionality than as different types of join index.
You can also create a column‑partitioned version of a single‑table simple join index.
All join index types can also be sparse, meaning that only the set of rows that satisfy a WHERE clause restriction in the index definition are included in the index (see “Sparse Join Indexes” on page 556).
See Chapter 11: “Join and Hash Indexes” and SQL Data Definition Language for further information.