Single-Table Join Index Definition
The definition for the join index might look something like this.
CREATE JOIN INDEX PartKeyLineItem AS
SELECT l_PartKey, l_Quantity, l_SupplierKey
PRIMARY INDEX (l_PartKey);
The intent of defining this join index is to permit the Optimizer to select it in
place of the base table LineItem in cases like the equality condition
l_PartKey = p_PartKey, eliminating the need to redistribute the LineItem table (because its proxy, the join index table PartKeyLineItem, has the same primary index as that of the Part table, so the rows are stored on the same AMP). This avoids the large redistribution
of LineItem, but not the join processing.
Not only can the Optimizer use single‑table join indexes for rewriting queries, it can also use statistics collected on complex expressions in the index definition to better estimate single‑table cardinalities. See SQL Request and Transaction Processing for more information about using hash and single‑table join indexes to estimate single‑table cardinalities.