Re: The truth behind UPDATE STATISTICS...

} From: rizzo@fourgee.demon.co.uk (Rus Ambler) } Subject: The truth behind UPDATE STATISTICS... } Date: Wed, 26 Apr 1995 19:33:50 +0000 } Reply-To: rizzo@fourgee.demon.co.uk } Organization: 4GEE Computer Systems } } H'ok, you probably think this is a dumbarse question, but apart from the } various system catalogs, what else does UPDATE STATISTICS update? } } If I run two identical SELECTs under ISQL on two simultaneous sessions, } having executed an UPDATE on one session, the optimizer takes different } index paths (result being either an instant response on the 'UPDATEd' session } or five minutes plus on the other). } } I've checked the obvious things like colmin, colmax and the values stored on } sysindexes and they are identical in both sessions. } } UPDATE STATISTICS must be doing something else, but what? } } The box in question is an HP 9000/857 running 9.08 BLS & Online/Secure } v5.00UD4 with a single label instance. } } --------------------------------------- } Rizzo rizzo@fourgee.demon.co.uk } --------------------------------------- This is just an educated guess; Jonathan Leffler or one of the other good folks with access to Informix "innards" could give you a more definitive answer. Since you just ran UPDATE STATISTICS on one session, I would suppose that a great deal of index info is retained in the engine's cache memory. That could improve performance on an index-intensive query. You said that the optimizer chose different index paths in the two sessions. The index(es) left in the cache might not be the ones that the optimizer would choose for a "cold start"/empty cache condition. An optimizer that is smart enough to see what is in cache and use it is a rather impressive piece of work. Regards, Alan +---------------------------+-----------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Lockheed Martin, SLS | Voice: 303-977-9998 | | P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. | | Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. | +---------------------------+-----------------------------------------------+