Archives of the TeradataForum

Message Posted: Fri, 14 Nov 2003 @ 14:12:21 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

If all else fails read the manual I said to my self. The real answer is not quite so simple and my recollection only partially served me.

From the V2R5 Database Design Reference Manual: Chapter 11, Pages 43-46.

"Collect statistics on the indexes of a join index to provide the Optimizer with the information it needs to generate an optimal plan."

"As far as the Optimizer is concerned, a multitable join index and the base tables it supports are entirely separate entities. You must collect statistics on multitable join index columns separately from the statistics you collect on their underlying base table columns because the column statistics for multitable join indexes and their underlying base tables are not interchangeable."

"
Note the following guidelines for collecting statistics for join indexes.

* To improve the performance of creating a join index and maintaining it during updates, collect statistics on its base tables immediately prior to creating the join index.

* Collect statistics on all the indexes defined on your join indexes to provide the Optimizer with the information it needs to generate an optimal plan.

* Collect statistics on additional join index columns that frequently appear in WHERE clause search conditions, especially when the column is the sort key for a value-ordered join index because the Optimizer can then use that information to more accurately compare the cost of using a NUSI-based access path in conjunction with range or equality conditions specified on the sort key column.

* In general, there is no benefit in collecting statistics on a join index for joining columns specified in the join index definition itself. Statistics related to these columns should be collected on the underlying base tables rather than on the join index.

The only time you gain an advantage by collecting statistics on a join column of the join index definition is when that column is used as a join column to other base tables in queries where the join index is expected to be used in the Optimizer query plan.
"

"On the other hand, it is generally preferable to collect statistics on the underlying base table of a single-table join index and not directly on the join index columns."

There's a lot of information in those few pages. In the interests of brevity I chose not to quote the entire set of documentation on this.

Further, in the Design Guide the recommendation for Hash Indexes (which is what my memory must of connected to) is to collect on the underlying base table columns.

Regards,

AGS

	<Prev	Next>		<<First	<Prev	Next>	Last>>