Statistics for multitable join indexes and the base tables they index are not interchangeable. Whenever a column is defined both for the base table and for a multitable join index defined on it, you must collect statistics separately on both columns.
The demographics for column values stored in a base table are often very different than those stored for a derived join result in a multitable join index. When statistics have not been collected on a multitable join index, the Optimizer does not derive corresponding statistics from the statistics collected on its underlying base tables.
You can collect statistics on a simple data column or secondary index column set of a join index. It is instructive to think of a multitable join index as a special base table representing a derived join result. You might need to collect statistics on search condition keys to help the Optimizer to evaluate alternative access paths.
Execution plans can also involve joining a multitable join index table with yet another table that is not part of the multitable join index. When your EXPLAIN statements indicate that this is happening, you should also collect statistics on those join columns to help the Optimizer to estimate cardinalities.
Before creating a new multitable join index, you should consider performing the following procedure to improve the performance of its creation as well as its update maintenance.
- Collect statistics on the base tables underlying the planned multitable join index.
- Immediately create the new multitable join index.