Requests that can use join indexes can run faster than queries that do not use them. Performance improves whenever the Optimizer can rewrite a request to use a join index instead of the base tables specified by the query.
A join index is most useful when its columns can cover most or all of the requirements in a request. For example, the Optimizer may consider using a covering index instead of performing a merge join.
Covering indexes improve the speed of join queries. The extent of improvement can be dramatic, especially for requests involving complex, large-table, and multiple-table joins. The extent of the improvement depends on how often an index can be used to rewrite a query.
In-place join indexes, where the columns of the covering index and the columns of the table to which the index is to be joined reside on the same AMP, outperform indexes that require row redistribution. An in-place, covering, aggregate join index that replaces 2 or more large tables in requests with complex joins, aggregations, and redistributions can enable a request to run hundreds of times faster.