Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns
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.
Note: This recommendation is not true for sparse join indexes (see “Sparse Join Indexes”). If the join index is sparse, you should collect statistics on the join index itself rather than on its underlying base table.
This recommendation is also not true for 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” on page 375 and SQL Request and Transaction Processing for details).
There are three reasons why you might need to collect statistics on single-table join index columns instead of their underlying base table columns.
- The single‑table join index is sparse (see “Sparse Join Indexes” on page 371).
- 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” on page 196).
- 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.