The definition for the join index may look like this.
CREATE JOIN INDEX PartKeyLineItem AS SELECT l_PartKey, l_Quantity, l_SupplierKey FROM LineItem PRIMARY INDEX (l_PartKey);
The intent of defining this join index is to permit the Optimizer to select the index 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.
The Optimizer can also use statistics collected on complex expressions in the index definition to better estimate single-table cardinalities. See Using Join Index Statistics to Estimate Single-Table Expression Cardinalities for more information about using single-table join indexes to estimate single-table cardinalities.