Re: npused <0
Hi guys: This is update statistics bug is a hugh pain! There is a well documented fix: It's a shell which updates npused and fixes the parser without recreating any tables. I don't have it with me at this moment, but if I don't see the fix in the newsgroup tonight, i'll post it my self tomorrow. Promise! 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 >