Re: Update statistics for table with more then 2^31 records

On Jun 22, 2:29 pm, "Alexey Sonkin" <alex...@cidc.com> wrote: > Hi, everybody, > > We've run into a funny problem with our DataWarehouse database: > > update statistics HIGH and MEDIUM doesn't work for tables with more> > then 2^31 = 2147483648 records (no need to say, that table is > fragmented) > > Operation finishes immediately without any error, and doesn't create any > SYSDISTRIB record. > > Also, SYSTABLES always gives 2^31-1 records for big tables: NROWS is of > type INT4... Yes, but the nrows in systables is not used for anything on a fragmented table, it would be the nrows in sysfragments for each fragment instead. In any event, since these values are UPDATED by update statistics, it's actually the NROWS values in sysptnhdr, whichis also by fragment, that is used to calculate bucket sizes, etc. This is just to note that the fact that these fields are INT4 is irrelevant. There seems, as ScottishPoet pointed out with his link, there's a bug in UPDATE STATISTICS MEDIUM in certain releases for larger tables. Try using HIGH for now, and definitely follow up with your Tech support call and give them the bug number noted in the link. Art S. Kagel > One can conclude, that 'update statistics' was never supposed to work > for bigger tables. > > I've filed a PMR to IBM 'passport advantage' support

Various issues with update statistics on large tables have been fixed in versions 9.40.xC9, 10.00xC6 and Cheetah. See following APAR's For 9.40.xC9: http://www-1.ibm.com/support/search.wss?rs=0&lang=en+en&loc=en-US&from=tss&ics=iso-8859-1&cs=utf-8&cc=us&q=IC51558&ibm-search=Search For 10.00xC6: http://www-1.ibm.com/support/search.wss?rs=0&lang=en+en&loc=en-US&from=tss&ics=iso-8859-1&cs=utf-8&cc=us&q=IC51560&ibm-search=Search In 9.40xC9 and 10.00xC6, if an overflow occurs, the following columns have been rounded off to their maximum value (2^31). In Cheetah, a complete fix has been introduced and these columns have been changed from integer type to double type. systables: nrows, npused sysindices: nleaves, nunique, clust sysfragments: nrows, npused Regards, Nita. "Art S. Kagel" <art.kagel@gmail.com> Sent by: informix-list-bounces@iiug.org 06/25/2007 09:18 AM To informix-list@iiug.org cc Subject Re: Update statistics for table with more then 2^31 records On Jun 22, 2:29 pm, "Alexey Sonkin" <alex...@cidc.com> wrote: > Hi, everybody, > > We've run into a funny problem with our DataWarehouse database: > > update statistics HIGH and MEDIUM doesn't work for tables with more> > then 2^31 = 2147483648 records (no need to say, that table is > fragmented) > > Operation finishes immediately without any error, and doesn't create any > SYSDISTRIB record. > > Also, SYSTABLES always gives 2^31-1 records for big tables: NROWS is of > type INT4... Yes, but the nrows in systables is not used for anything on a fragmented table, it would be the nrows in sysfragments for each fragment instead. In any event, since these values are UPDATED by update statistics, it's actually the NROWS values in sysptnhdr, whichis also by fragment, that is used to calculate bucket sizes, etc. This is just to note that the fact that these fields are INT4 is irrelevant. There seems, as ScottishPoet pointed out with his link, there's a bug in UPDATE STATISTICS MEDIUM in certain releases for larger tables. Try using HIGH for now, and definitely follow up with your Tech support call and give them the bug number noted in the link. Art S. Kagel > One can conclude, that 'update statistics' was never supposed to work > for bigger tables. > > I've filed a PMR to IBM 'passport advantage' support _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list