npused <0

>> 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