UPDATE STATISTICS and COUNT(*)
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 tohave 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.
2. SELECT COUNT(*) is much faster under OnLine (newer versions, at least)
than under (older versions of) Standard Engine.
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.
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.
O ___________________________ Regards,
H | R. Alan Popiel |__________________ Alan
H | Martin Marietta, Tech Ops | Internet: |_________________________
H | P.O. Box 179, M/S 5422 | alan@den.mmc.com | /
H | Denver, CO 80201-0179 USA | Voice: | Std disclaimers apply./
H |___________________________| 303-977-9998 | (
H (_____________________| (But you knew that!) \\
H (___________________________\\