Single-table join indexes can support alternative access paths to data, and are therefore useful in tactical applications. Consider single-table join indexes when a tactical query carries a value in an equality condition for a column (like a customer phone number) that is in the table but not in its primary index (a customer key, for example).
A single-table join index can be constructed using the available non-indexed column, the customer phone number, as its primary index, thereby enabling single-AMP access to the data and avoiding more costly all-AMP nonprimary index access to the base table.
Single-table join indexes are also valuable when your applications often join the same large tables, but their join columns are such that row redistribution is required. A single-table join index can contain the data required from one table, but using a primary index based on the FK of the table, preferably the primary index of the table to which the index is to be joined.
A single-table join index can also create an index subtable that contains frequently accessed columns from a table with columns that are rarely accessed.
Use of such an index greatly facilitates join processing of large tables, because the single-table index and the table with the matching primary index both hash to the same AMP.
The Optimizer evaluates whether a single-table join index can replace or partially cover its base table even when the base table is referenced in a subquery unless the index is compressed and the join is complex, such as an outer join or correlated subquery join.