Re: Q: Estimated Cost from SET EXPLAIN?

George Follis (follis@esd.dl.nec.com) wrote: : George Follis (follis@esd.dl.nec.com) wrote: : <Stuff about the query and results of set explain - removed> : I will follow-up on some of the replies. And some other information : that may or may not make a difference. : The ESQL/C is 5.01. The Online Engine is 5.01. We are running UPDATE : STATISTICS on a regular basis (every hour). This is a hotel application : and they have massive updates and deletes all day long. I'd like to clarify my colleague's questions, since I was involved in the investigation, too. What we noticed was that merely reversing the order of the query's two tables -- "directory" and "occupant" -- impacted the performance of the query. The Directory Table had almost twice the records (6,000 to 3,700) of the Occupant Table, and each Directory record was about 4 times the size of an Occupant record. Obviously, a sequential scan of the Directory Table should have been avoided if at all possible. We had indexes on the fields that were involved in the WHERE clause for both tables. Yet in one case, the optimizer did a sequential scan of the Directory Table; in the other case, it used the index. 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