Query Plan: Join Index Defined - Teradata Database

Query Plan: Join Index Defined

Now consider the execution plan for this same query when the following join index has been defined:

     CREATE JOIN INDEX OrdCustIdx AS

      SELECT (o_custkey, c_name), (o_status, o_date, o_comment)

      FROM orders, customer

      WHERE o_custkey=c_custkey;

With this join index defined, the execution plan for the query specifies a simple scan of the join index without accessing any of the underlying base tables and without having to join them on the predicate WHERE o_custkey = c_custkey.

In the join index defined for this example, (o_custkey, c_name) is the specified fixed part of the index and (o_status, o_date, o_comment) is the repeated portion. Therefore, assume the following specimen base table entries (where the ? character indicates a null).

Customer
CustKey	Name	Address
100	Robert	San Diego
101	Ann	Palo Alto
102	Don	El Segundo

Orders
OrderKey	Date	Status	CustKey	Comment
5000	2004-10-01	S	102	rush order
5001	2004-10-01	S	100	big order
5002	2004-10-03	D	102	delayed
5003	2004-10-05	U	?	unknown customer
5004	2004-10-05	S	100	credit

You cannot collect statistics on a complex expression from a base table. If your applications frequently run queries that specify complex expressions in their predicates, you should consider creating a single‑table join index that specifies a matching complex expression in its select list or column list, respectively. When Teradata Database creates the index, it transforms the complex expression into a simple index column on which you can collect statistics.

If the complex expression specified by the index is a term that matches a predicate condition for a query made against the base table the index is defined on, statistics collected on the index expression can be mapped to the base table so the Optimizer can use them to make more accurate single‑table cardinality estimates.