The Optimizer substitutes base table statistics for single-table join index statistics when no demographics have been collected for its single-table indexes. Because of the way single-table join index columns are built, it is generally best not to collect statistics directly on the index columns and instead to collect them on the corresponding columns of the base table. This optimizes both system performance and disk storage by eliminating the need to collect the same data redundantly.
Also, this recommendation does not apply to the case where a single-table join index is defined using a complex expression in its select list that is frequently specified in predicates used in queries made against the mapped base table columns in the expression. See Collecting Statistics on Join Indexes and Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.
- The single-table join index is sparse (see Sparse Join Indexes).
- If you decide not to collect the statistics for the relevant base table columns for some reason, then you should collect them directly on the corresponding single-table join index columns.
- If the primary index column set consists of more than one column, and there is no primary or secondary index on the base table that includes those columns, then you have two options:
- The better option is to collect multiple column statistics on the base table (see Collecting Statistics on Multiple Columns).
- A much poorer option is to collect the statistics on the column set for the single-table join index. If you do this, you must use the COLLECT STATISTICS ON … INDEX syntax to collect the statistics for that column set on the single-table join index.