Collecting Statistics on Multitable Join Indexes - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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.

  1. Collect statistics on the base tables underlying the planned multitable join index.
  2. Immediately create the new multitable join index.