Update Statistics doesn't

We just tracked down an elusive problem that a client was having. The symptoms were that a particular query was running out of resources. A SET EXPLAIN revealed that the estimated cost and number of rows were both pegging MAXINT. When the costs start getting that high, you're glad that the machine can't handle it, because obviously OnLine will try anyway. Instead it dumped immediately. Anyway, the only result that seem to fix the problem was rebuilding the indexes for the tables in question. That's bothersome. What was more bothersome was that the indexes seemed to be corrupted every day. We were extremely perplexed about what could be corrupting the indexes. tbcheck said everything was okay, but for whatever reason the optimizer was getting all blown out with its estimates. Turned out that the client had a job running UPDATE STATISTICS every night on the system. These statistics then influenced the optimizer is some odd chaotic way. What was really odd about it all, was that the execution plan made sense, at least regarding what indexes to use when and such. But the numbers were way off. In one example, before the indexes on the tables were rebuilt, the estimates was in the millions-billions. Rebuilding indexes on one table (of three in this query) brought back costs bewteen 200-300,000. Once the final indexes were rebuilt, the costs dropped to < 100. The actual path didn't change. Anyway, I'm sure it's a bug of somekind. But, the clients next logical step is to go from 4.1 to 5.0+, but that means going from SunOS to Solaris, so they're not eager to update everything all at once. Now, something that I don't quite understand. After looking at the SYSINDEXES table before and after the rebuild and UPDATE STATISTICS, some columns have odd vaules. At the end of the SYSINDEXES table are some columns, (level, leaves, unique, etc). After an UPDATE STATISTICS, these columns all have values. After recreating the index, they're NULL. Wouldn't you think that rebuilding an index would be somewhat identical to an UPDATE STATISTICS for that index? I was surprised to find them null. Well, I thought I would share that experience with everyone. We've decided to just write a script that recreates all of the indexes and run it periodically after the UPDATE STATISTICS. But if anyone else has any insight into how the statistics may be represented in the system and such, I'd love to here them. Will la4gen!villy@uunet.uu.net