Re: npused <0

In <3v42c4$mmm@ixnews4.ix.netcom.com> olded@ix.netcom.com (Ed Schaefer ) writes: > There is a fix for the On-line 5.X npused/update statistics bug described below: For 4K page size: update systables set npused = nrows/(4068/(rowsize + 4)) where tabtype = "T" For 2K page size: update systables set npused = nrows/(2020/(rowsize + 4)) This must be run by user informix. Although, this originally came from Informix and I'm using it myself, the usual cautions apply. IT AIN'T MY FAULT OR JETECH DATA SYSTEMS FAULT IF IT DOESN'T WORK OR SOMETHING NASTY HAPPENS! regards, ed schaefer > >In <3v1jmm$7v9$1@perth.DIALix.oz.au> rswa@perth.DIALix.oz.au (Radio >Spares) writes: >> >>>> Curtis Kling wrote: >>>>I'd like to clarify my colleague's questions, since I was involved >in the >>>>investigation, too. >>>> >>>> <stuff deleted> >>>> >>>>I examined the systables record for "directory", both before and >after >>>>performing UPDATE STATISTICS. While the number of rows changed, the >number >>>>of pages used (npused) remained the same -- negative! I also checked >the >>>>systables record for "occupant". Here are the numbers: >>>> >>>>BEFORE UPDATE nrows npused >>>> ----- ------ >>>>directory 5746 -11311 >>>>occupant 3730 65 >>>> >>>>AFTER UPDATE nrows npused >>>> ----- ------ >>>>directory 6157 -11311 >>>>occupant 3735 65 >>>> >>>> <more stuff deleted> >>>> >>>>------------ >>>>Curtis Kling >>>>NEC America, Irving TX >>>>kling@esd.dl.nec.com >> >>Dear Curtis, >> >>This is the much discussed update statistics bug which is meant to be >fixed in >>version 7. Becuase the npused figure is <0 the optimiser gets a >negative >>cost figure when it checks to see if a sequential scan will be a fast >access >>method. Becuase it is negative it decides that this must be a good >thing and >>goes ahead with the sequential scan. >> >>You can modify the systables entry and correct this value but if you >do the next >>time that update statistics runs it will just get set back to a <0 >number >>again. >> >>The only way I have found to stop this is to unload the data, drop the >table >>and reload it again. Also this always seems to happen on very large >tables. >>So it is best to manually update the systables entry so that the users >>can use the system and then on the weekend or at night to the >load/unload. >> >>Hope this helps. >> >>Jason Harris (RSWA) >>RS Components >> >