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

 < Last>>

 Subj: Re: PI and statistics From: Doug Drake

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,

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2002 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback