Archives of the TeradataForum

Message Posted: Tue, 11 Dec 2007 @ 09:34:34 GMT

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

Jon gave a pretty good picture of it. Thanks Jon. To summarize,

Collecting single-column statistics on an index(using INDEX syntax) or on a column (using COLUMN syntax) has no difference.

Usage part: Optimizer is smart enough to use them for both index access costing and for selectivity estimations(sigle table, join cardinalities, aggregate estimations, etc.) on this colum.

Maintenance: Dropping the index DOESN'T drop the single-column stats. The ORDER of collcting statistics and creating index makes no difference.

Collecting multicolumn statistics on an index(using INDEX syntax) or on the set of columns(using COLUMN syntax) have few minor differences.

Usage part: Optimizer is smart enough to use them for both index access costing and for selectivity estimations(single table estimations for EQUALITY predicates, join cardinalities, aggregate estimations, etc.) on the set of columns.

Maintenance: The ORDER of collecting stats and creating index has some difference in terms of maintenance. If the index is created first followed by stats collection (using INDEX or COLUMN syntax), the statistics are associated with the index and will be DROPPED when index is dropped. If the stats are collected first followed by the creation of index, the stats WON'T BE DROPPED when the index is dropped.

P.S: There is a bug before which didn't not use multicolumn stats for indexes(when the stats are collected first followed by the creation of index). This has been fixed long back and available from release v2r5.1.2.

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