Re: UPDATE STATISTICS and COUNT(*)

In <21kbb8INN5nt@emory.mathcs.emory.edu> alan@po.den.mmc.com (Alan Popiel) writes: >Fellow netters, >I have learned a great deal from the recent protracted exchange regarding >UPDATE STATISTICS and COUNT(*), part of which occurred off-net. I would >like to summarize some of what I found valuable for my colleagues on the net. >I work exclusively with Standard Engine databases, so the info about >UPDATE STATISTICS under OnLine was very illuminating. Also there seem to>have been changes in this area with recent version releases. >What I have gleaned is this: >1. Informix OnLine keeps *many* more statistics in the system catalogues >than does Informix SE. A (possibly incomplete) list of these includes: >- systables.nrows >- systables.npused >- syscolumns.colmin >- syscolumns.colmax >- sysindexes.levels >- sysindexes.leaves >- sysindexes.nunique >- sysindexes.clust >Many thanks to Graeme Sargent for this info. As recently as version 4.10.UD1, >by my own checking, only systables.nrows was included in Standard Engine DBs. >I don't have access to 5.00 DBs and thus cannot report on them. This is still true in SE 5.00. >2. SELECT COUNT(*) is much faster under OnLine (newer versions, at least) >than under (older versions of) Standard Engine. Not only that, but current OnLine is faster than older OnLine/Turbo (which used to do a scan). Current SE is faster than older SE (which also used to do a scan). I'm afraid I don't know at what revision the change was made. >3. SELECT COUNT(*) FROM ... is much faster under OnLine than UPDATE STATISTICS >FOR TABLE ... followed by SELECT systables.nrows WHERE tabname = ..., while >the reverse was true for (older versions of) Standard Engine. But this misses the point. There is no benefit in even risking the possible inaccuracy of SELECT systables.nrows ... as SELECT COUNT(*) ... is now just as fast or faster. This is true for both OnLine and SE. >Gosh, summarized like this, there seems to be less here than the recent torrent >of comments would indicate. Never-the-less, I hope this summary is useful to >my friends and colleagues. Hmmm... graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.