Archives of the TeradataForum

Message Posted: Sat, 24 Aug 2002 @ 14:52:18 GMT

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

Hiroshi,

It sounds like you might be noticing this difference due to the way statistics are collected and stored. I believe there are a maximum of 100 intervals for each statistic collected. The sample below (extracted via TSET) shows the type of information collected for a single column statistic. I have included only the first 10 of the 100 intervals for this column. Interval 0 applies to the entire set of intervals.

If the predicate includes a value that exactly matches one of the interval mode values then the exact number of rows should be used as the estimate. If the value is any one of the values between and including the min or max values it likely calculates the number relative to the total number of rows in the interval which is likely to be fairly close to the actual count.

This brings us to the following conclusions: The less uniform the distribution the less accurate the estimate provided to the optimizer. Also, the larger the number of unique values the less accurate the estimates might be. And finally, the above is generally applicable for columns containing more than 100 unique values as the number of intervals will always be less than or equal to the number of unique values.

Regards,

Doug Drake

/* Interval[0] */
/* NumNulls */ 0,
/* NumIntervals */ 100,
/* Table Summary */
/* Min. Value */ 'ABAAND ',
/* Mode Value */ 'AXXRECON ',
/* Mode Freq. */ 2,
/* Total Values */ 270,
/* Total Rows */ 504,
/* Interval[1] */
/* Max. Value */ 'AXXXCON ',
/* Mode Value */ 'ACCREXX ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[2] */
/* Max. Value */ 'AGRLTERM ',
/* Mode Value */ 'AGRITERM ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[3] */
/* Max. Value */ 'ANLINVACC ',
/* Mode Value */ 'ANLINCHK ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[4] */
/* Max. Value */ 'ARMS ',
/* Mode Value */ 'ARMS ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[5] */
/* Max. Value */ 'BAIRCRFT ',
/* Mode Value */ 'ARPURCH ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[6] */
/* Max. Value */ 'BAUTONEW ',
/* Mode Value */ 'BAUTONEW ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[7] */
/* Max. Value */ 'BCCCCRD ',
/* Mode Value */ 'BAXTOUSD ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[8] */
/* Max. Value */ 'BINSTLCJ ',
/* Mode Value */ 'BECOCHK ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[9] */
/* Max. Value */ 'BINSTLOT ',
/* Mode Value */ 'BINSTLOT ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,
/* Interval[10] */
/* Max. Value */ 'BINVACC ',
/* Mode Value */ 'BINSTLXXS ',
/* Mode Freq. */ 2,
/* # Other Val. */ 1,
/* # Other Rows */ 2,

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