Single- and multitable join indexes are fundamentally different database objects in many ways, and they are used for different purposes.
As with hash indexes, column statistics for single-table join indexes are generally best collected directly on the base table columns rather than separately as is always required for multitable join indexes.
An important exception to this guideline is the case where a single-table join index is defined on a complex expression that is also frequently specified as a term in predicate expressions for queries made on the relevant base table column. In this context, a complex expression is defined as an expression that specifies something other than a simple column reference on the left hand side of a predicate.
Always consider using the SAMPLE options when you collect and recollect statistics on a join index. See Reducing the Cost of Collecting Statistics by Sampling for further information about these options and recommendations on how to use them.
The Optimizer can only use statistics collected on complex expressions that can be mapped completely to a single-table join index or hash index expression. Collecting statistics on the join index column for those expressions enhances the ability of the Optimizer to estimate single-table cardinalities for a query that specifies the base table expressions in its predicate. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
The following table points to the various topics that explain the different recommendations for collecting statistics for single-table and multitable join indexes:
FOR this type of join index … | The following topics explain how best to collect statistics on the index … |
---|---|
single-table | |
multitable | Collecting Statistics on Multitable Join Indexes. |
sparse |
For general information about collecting statistics, see COLLECT STATISTICS (Optimizer Form).