Materialized Join Index - Teradata Database

Materialized Join Index

The resulting join index rows would be the following (where the ? character indicates a null).

OrdCustIdx
Fixed Part		Repeated Part
CustKey	Name	Status	Date	Comment
100	Robert	S	2004-10-01	big order
		S	2004-10-05	credit
101	Ann	P	2004-10-08	discount
102	Don	S	2004-10-01	rush order
		D	2004-10-03	delayed
?	?	U	2004-10-05	unknown customer

With the join index defined in this way, the following query could be resolved using just the join index, without having to scan the base tables.

     SELECT o_status, o_date, o_comment

     FROM orders;

In this particular case, it is more efficient to access the join index than it is to access the orders base table. This is true whenever the cost of scanning the join index is less than the cost of scanning the orders table. The Optimizer evaluates both access methods, choosing the more efficient, less costly of the two for its query plan.