The usefulness of a join index depends on the type of work the index is designed to perform. Prototype any join index and evaluate its usefulness to the supported applications before adding it to your production environment. The overhead of updating join index tables can outweigh their benefit in some situations.
- Frequent joins of large tables with other large or moderately-sized tables that cause a significant number of the rows from both tables to be joined.
- Frequent joins of tables of high degree (having a large number of columns) for which the same relatively small set of columns is repeatedly requested.
- An alternate partitioning sequence for a vertical subset of data in one of the base tables (a so-called single-table join index) removes the necessity of redistributing rows for a frequently made join.
- The overhead in time and storage capacity for the creation and maintenance of a join index does not outweigh its retrieval benefits.
- The performance of frequent range queries requiring joins of large tables with other large or moderately-sized tables that cause a significant number of the rows from both tables to be joined.
- A row-partitioned join index can enhance the performance of queries if you specify an equality or range constraint on the partitioning column set. For example, a single-table row-partitioned join index can take advantage of row partition elimination to improve both the performance of a query retrieving rows from itself.
You cannot define row partitioning for a row-compressed join index.
- If a frequently run query specifies a complex expression in its predicate, consider creating a single-table join index on the table that includes that expression in the select list or column list, respectively, of its definition. Although you cannot collect statistics on complex base table expressions, creating a single-table join using the expression transforms it into a simple column, and you can then collect statistics on that column. The Optimizer can then use those statistics to estimate the single-table cardinality of evaluations of the expression in a query predicate that specifies the expression using a base table column. For more information, see Using Join Index Statistics to Estimate Single-Table Expression Cardinalities .
- Most queries against a column-partitioned table or join index are expected to be selective on a variable subset of columns, and project a variable subset of the columns where the subset of accessed columns is less than 10% of the column partitions for any query.
You may need to experiment.
For example, application of a row-partitioned join index may be for queries that involve row-partitioned base tables. However, if the base table is not a row-partitioned table, but is designed to handle efficient joins on the primary index, a row-partitioned join index may be defined to provide an alternative organization of the data for optimal access based on row partitions. This is only valid if the join index is not row-compressed. Partitioning is not valid for row-compressed join indexes.