Re: Q: Estimated Cost from SET EXPLAIN

Curtis Cling/George Folis wrote: >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 >Our software runs at several hotels (including the MGM Grand in Las Vegas) >but only one site has a negative "npused". My question is, what does a >negative value mean for "npused"? How can the number of pages used by a table >be negative? Would this value affect the optimizer's decision? >You may reply to either George or me. Thank you. >------------ >Curtis Kling >NEC America, Irving TX >kling@esd.dl.nec.com I have seen this problem of -ve npused before (Online 5.03) and solved it by UPDATING thenpused values to 0 where they were -ve and running UPDATE STATISTICS again. I do not know why npused goes negative, or why UPDATE STATISTICS seems to fail to update the stats for tables with -ve npused values. Perhaps Johnathan Leffler (sorry if i've misplt the name) can shed some light on this one? Mark Denham BBC London, UK Mark.Denham@bbc.co.uk