15.00 - Decision: Ordinary Join Index Versus Single-Table Join Index - Teradata Database

Teradata Database Design

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

Decision: Ordinary Join Index Versus Single-Table Join Index

You could create an ordinary join index on the LineItem and Part tables, but there is a high cost to keeping this join index updated because each update requires a costly minijoin operation, so the ordinary join index would have to be updated each time either a line item or a new part was inserted, deleted, or updated in the respective primary base tables.

A better solution might be to create a single-table join index on the columns of LineItem that need to be joined frequently with the Part table and then make the primary index for the join index l_PartKey. Single-table join indexes are not cost-free, but the cost of performing the single row updates for a single-table index is far less expensive than the minijoins required by a multitable join index.