15.00 - Single-Table Join Index Definition - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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
      FROM LineItem
      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.