You can define several types of join index, each having its unique role in enhancing the performance of your database queries without denormalizing the base tables that support your ongoing ad hoc data warehouse activities. Because join index tables cannot be accessed directly by users and are not part of the logical design for a database, they can be used to create persistent prejoin and summary tables without removing or otherwise lessening the ability of your databases to support a wide range of ad hoc queries.
You can also create views whose SELECT definitions are identical to those of a join index, then whenever a user accesses data using that view, the similarly-defined join index should cover the request, and thereby be selected by the Optimizer for the query plan it develops (unless it discovers and chooses a plan that is less costly).
Note that any join index type can be defined with a row-partitioned primary index as long as it is not also row-compressed.
Following is a summary of the join index types and their common uses.
|Join Index Type||Description|
|Simple||A join index table defined without aggregation.|
|Single table||Several potential applications exist.
See Single-Table Join Indexes for additional information.
|Multitable||A column subset of two or more major tables that are frequently joined defined as a prejoin of those tables.
The defined prejoin, or join index, permits the Optimizer to select it to cover frequently made join queries rather than specifying that its underlying base tables be searched and joined dynamically.
|Aggregate||A join index table defined with aggregation on one or more of its columns.|
|Single-table aggregate||A column subset of a base table defined with additional columns that are aggregate summaries of base table columns.
|Multitable aggregate||A column subset, including aggregates defined for one or more columns, of two or more major tables that are frequently joined defined as a prejoin of those tables.
The defined prejoin, or join index, permits the Optimizer to select it to cover frequently made join queries that also compute aggregates rather than specifying that its underlying base tables be searched, aggregated, and joined dynamically.
|Sparse||Any join index that limits its rows to those satisfying a constant condition in the WHERE clause of its definition.
A sparse join index permits the database designer to limit the rows in the index to a tightly filtered subset of the rows in the component base table set. A common use of this feature might be to restrict the population of a join index to only those rows that are frequently accessed by a commonly performed query or set of queries.
|Column-partitioned||Useful in support of DML requests that access a variable, selective, small subset of the columns (as specified in predicates or projection lists) and rows of a column-partitioned table. Also useful in support of RowID joins between a column-partitioned table and another table.
Column-partitioned join indexes have the following restrictions: