A Join Index (JI) is composed of columns from one or more base tables. Like a base table, a JI can have a primary index, which can be used for direct access to one or a few rows.
A JI covers a query when the JI includes all the columns the query references. A covered query needs to examine only the JI.
A JI partially covers a query when the JI includes only some columns the query references. A partially covered query can use the JI for the columns it includes, but must get the other columns from the base tables.
You can define aggregate JIs for commonly used aggregation queries.
You can join a nonaggregate JI to its base tables to retrieve columns referenced by a query but not stored in the JI.
JIs impose additional processing on insert and delete operations and update operations that change the values of columns stored in the JI. The performance trade-offs are similar to those for SIs.