15.00 - Joins With a Column-Partitioned Table, Join Index, or Spool - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Joins With a Column‑Partitioned Table, Join Index, or Spool

Teradata Database can directly access a column‑partitioned table, join index, or spool using a dynamic hash join or product join. This means the column‑partitioned object must be joined to a duplicated spool, and that spool must be relatively small for the join to be efficient.

The system can also directly access a column‑partitioned database object using a RowID join. In this case, the rowIDs must come from a secondary index or from a join index if the table is column‑partitioned, on the column‑partitioned object, or from a previous retrieve or join to the column‑partitioned object.

Other joins methods are possible, but to use those methods, Teradata Database must first construct the selected rows from the column partitions and then spool them, possibly with a redistribution operation and local AMP sort or duplication to all AMPs. This might be a reasonable plan if only a few rows are selected or if only a few columns are needed from the column‑partitioned object.

If a join index is applicable, the Optimizer can make use of the index without actually having to join the tables.